Обсуждение: returned value from SQL statement

hi,

i would like to know how can i control the returned value from a SQL statement ?
for example, if i do an INSERT INTO... :
1. how can i know if the INSERT worked ?
i mean i already trapped the unicity violation, but what if there is another error ? where can i get a complete list of exceptions ?

2. if the INSERT INTO is successful, how can i know it ? what is the returned value ?

3. the same question as point 2 but for all SQL statements from PostgreSQL.

I read few books and every time this part is not treated correctly but only partially.

thanks a lot,

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

Alain Roger wrote:
> hi,
>
> i would like to know how can i control the returned value from a SQL
> statement ?
>

You've left out some pretty significant information, like what method
you're using to access PostgreSQL.

ODBC from C/C++/whatever?

PHP?

Perl DBD?

Python DBI?

The .net interfaces?

Java / JDBC ?

Java with an ORM layer ?

PL/PgSQL stored proceures executed by `psql' ?

...

--
Craig Ringer

Alain Roger wrote:
> hi,
>
> i would like to know how can i control the returned value from a SQL
> statement ?
> for example, if i do an INSERT INTO... :
> 1. how can i know if the INSERT worked ?
> i mean i already trapped the unicity violation, but what if there is another
> error ? where can i get a complete list of exceptions ?
>
> 2. if the INSERT INTO is successful, how can i know it ? what is the
> returned value ?

If it fails you'll get an error. The transaction will be aborted. How
you access this error code will depend on what library/client you are using.

> 3. the same question as point 2 but for all SQL statements from PostgreSQL.

Same.

> I read few books and every time this part is not treated correctly but only
> partially.

There's a full list of error codes here if you want to know what went wrong:
   http://www.postgresql.org/docs/current/static/errcodes-appendix.html

Am I mis-understanding you?

--
   Richard Huxton
   Archonet Ltd

sorry... under pl/pgsql as stored procedure




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

thanks for the link regarding errors... i did not find it before.




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

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

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

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

Alain Roger wrote:
> i would like to know how can i control the returned value from a SQL statement ?
> for example, if i do an INSERT INTO... :
> 1. how can i know if the INSERT worked ?
> i mean i already trapped the unicity violation, but what if
> there is another error ? where can i get a complete list of
> exceptions ?

You can get almost every error message from an INSERT.
A few examples: 53100 (DISK FULL), 22004 (NULL VALUE NOT ALLOWED), ...
There could be a trigger or a rule involved which could
basically do anything in the database...

> 2. if the INSERT INTO is successful, how can i know it ? what
> is the returned value ?

"Returned" sounds like you are referring to a specific API.
Which programming language do you use?
An INSERT statement is successful iff it does not generate an error message.
Of course, this does not necessarily imply that something
was inserted - e.g., if there is an INSTEAD DO NOTHING rule
on the table.

> 3. the same question as point 2 but for all SQL statements
> from PostgreSQL.

Similar: an statement is successful iff it does not generate an error message.
I'd say that's the definition of a successful statement.
As to the return value, that depends on the kind of statement:
A DELETE (without RETURNING) or CREATE TABLE statement will not return rows,
while a SELECT or a DELETE ... RETURNING will.

Yours,
Laurenz Albe

Alain Roger wrote:
> the purpose here, it is to solve my problem with a transaction inside a function.

You cannot handle transactions inside a function (apart from using EXCEPTION blocks).
A PostgreSQL function is always executed in a single transaction.

All SQL statements you issue from within a function are in that same transaction.

Yours,
Laurenz Albe