not really.. but it is true that it can be confusing...sorry :-(
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 <[EMAIL PROTECTED]>
wrote:
> Alain Roger wrote:
> > sorry... under pl/pgsql as stored procedure
>
> Then this question was already asked and answered less than a week ago
> 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
