Re: returned value from SQL statement - Mailing list pgsql-general

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 :



--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008