Re: returned value from SQL statement

On Thu, Mar 27, 2008 at 11:25:18AM +0100, Alain Roger wrote:
> 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.

hum, I think PG works a little differently than you think. a function
is run inside a transaction, not the other way around. If there's an
error inside a transaction the whole transaction will be aborted (and by
implication all the code following the statement that caused the error).

> i need to know if there is a common return value for error in case of a SQL
> statement failed.

The return values from functions are for your consumption, they're not
to indicate transaction failure.

> it seems that not, so i would like to know if the rollback inside an
> EXCEPTION block is the best practice.

it's not really valid at all. savepoints[1] are about your best bet,
which I think are exposed as the EXCEPTION[2] statements you already
found.

I'm not sure I understand the point of the function anyway! all it seems
to be doing is enforcing a UNIQUE constraint. I'd just run:

ALTER TABLE cust_portal.users ADD CONSTRAINT users_email_uniq UNIQUE (email);

and then use the following function:

CREATE OR REPLACE FUNCTION cust_portal.sp_u_003(TEXT,TEXT,TEXT,BOOL,TEXT)
RETURNS void LANGUAGE sql AS $$
INSERT INTO cust_portal.users (usrname,firstname,email,nl_reg,nl_lang)
VALUES ($1,$2,$3,$4,$5);
DELETE FROM cust_portal.tmp_newsletterreg WHERE email = $3;
$$;

Sam

[1] http://www.postgresql.org/docs/current/static/sql-savepoint.html
[2] http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING