Search Postgresql Archives

Re: return value from SQL statement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux