the purpose here, it is to solve my problem with a transaction inside a function.
i need to know if there is a common return value for error in case of a SQL statement failed.
it seems that not, so i would like to know if the rollback inside an EXCEPTION block is the best practice.
here is an example :
CREATE OR REPLACE FUNCTION cust_portal.sp_u_003(usrname character varying, firstname character varying, email character varying, nl_reg boolean, nl_lang character varying)
RETURNS integer AS
$BODY$
DECLARE
existing_email INTEGER := 0;
BEGIN
set search_path = cust_portal;
SELECT count(*) INTO existing_email FROM users WHERE users.email = email;
IF (existing_email != 0) THEN
RETURN (-1);
ELSE
-- BEGIN TRANSACTION;
INSERT INTO cust_portal.users VALUES
(
nextval('users_usr_id_seq'),
usrname,
firstname,
email,
nlreg,
nl_lang
);
DELETE FROM cust_portal.tmp_newsletterreg WHERE tmp_newsletterreg.email = email;
COMMIT;
RETURN(0);
EXCEPTION
ROLLBACK;
RETURN(-2);
END IF;
END;
moreover such code generates an error :
On Thu, Mar 27, 2008 at 10:55 AM, Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> wrote:
Alain Roger wrote:Then this question was already asked and answered less than a week ago
> sorry... under pl/pgsql as stored procedure
on this mailing list.
By you, with almost exactly the same subject line.
Forgive my confusion, but why are you asking the same question again?
What's changed since last time? What else do you need to know?
--
Craig Ringer
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
