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
