Thread: return value from SQL statement

Hi,

i have a 'insert into' statement and i would like to know if it fails or not.
i was thinking to check the resturn value from this statement, but how to do it ?

thx.

--
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 have a 'insert into' statement and i would like to know if it fails or
> not.
> i was thinking to check the resturn value from this statement, but how to do
> it ?
>
>
The answer depends entirely on how you're using the database.

ODBC?

JDBC?

Perl DBD?

Python with psycopg?

SQL code read by the psql command?

Also, by "failure", do you mean "encountered an error that terminated
the transaction" or "inserted zero rows" ?

--
Craig Ringer

under pl/pgsql language i would like to return a function value.
e.g :
0 is everything is completed
-1 if searched data already exist into table
-2 if insert into failed
...

later i would like to use this returned value in my PHP (this i know how to do)
is it clearer ?
A.




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

On Tue, 25 Mar 2008 16:38:04 +0100
"Alain Roger" <raf.news@gmail.com> wrote:

> under pl/pgsql language i would like to return a function value.
> e.g :
> 0 is everything is completed
> -1 if searched data already exist into table
> -2 if insert into failed
> ...
>
> later i would like to use this returned value in my PHP (this i
> know how to do)
> is it clearer ?
> A.

some code snipplet

8<---8<---8<---8<---8<---8<---

create or replace function somefunc(param1 int, param2 int, out res
smallint) as

begin

...

end;

8<---8<---8<---8<---8<---8<---

res:=-1;

8<---8<---8<---8<---8<---8<---

GET DIAGNOSTICS _rowN = ROW_COUNT;

give a look to FOUND & Co.

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

8<---8<---8<---8<---8<---8<---

$result=pg_query("select res from somefunc(....)");
$row=pg_fetch_array($result);
$res=$row['res'];

8<---8<---8<---8<---8<---8<---

give a look to

http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

8<---8<---8<---8<---8<---8<---

you may even be interested in setof & Co. and some clarifying
examples:

http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html#extended

still your question look a bit too broad to give you one answer.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Alain Roger wrote:
> under pl/pgsql language i would like to return a function value.
> e.g :
> 0 is everything is completed
> -1 if searched data already exist into table
> -2 if insert into failed
> ...
>

Given the table:

CREATE TABLE testtable (
    blah VARCHAR,
    CONSTRAINT blah_is_unique UNIQUE(blah)
);

You could check uniqueness in your query manually (I assume you know how
to do that). You could use a subquery on INSERT and use GET DIAGNOSTICS
to find out if it did anything:

CREATE OR REPLACE FUNCTION insert_and_return(VARCHAR) RETURNS INTEGER AS $$
DECLARE
    num_rows_inserted INTEGER;
BEGIN
    -- Inserts blah=$1 into testtable only if a row with blah=$1 does
not already exists
    INSERT INTO testtable (blah) SELECT $1 WHERE NOT EXISTS (SELECT 't'
FROM testtable WHERE blah = $1);
    -- Finds out if we did anything
    GET DIAGNOSTICS num_rows_inserted := ROW_COUNT;
    RETURN CASE WHEN num_rows_inserted = 0 THEN -1 ELSE 0 END;
END;
$$ LANGUAGE 'plpgsql';

You could also just try the insert and trap a unique_violation. This is
likely to be useful if you have lots of complex referential integrity
constraints, CHECK constraints, etc too. Note, however, that EXCEPTION
gets really expensive if you're using it tens of thousands of times in a
single transaction.

CREATE OR REPLACE FUNCTION insert_and_return2(VARCHAR) RETURNS INTEGER AS $$
BEGIN
    BEGIN
        INSERT INTO testtable (blah) VALUES ($1);
    EXCEPTION
        WHEN unique_violation THEN
            RETURN -2;
        -- add more WHEN clauses here, or more exceptions to the WHEN
clause,
        -- for other conditions you want to trap.
    END;
    RETURN 0;
END;
$$ LANGUAGE 'plpgsql';

See:

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Personally it seems like a bit of a funny thing to be doing, though.
Aren't you better off performing the INSERT with a WHERE clause that
protects against collisions, constraint exclusions, etc, then using your
PHP database interface's diagnosics ( cursor.get_row_count() or whatever
it is in PHP ) to see whether the query did anything?

--
Craig Ringer