PostgreSQL 9.0.4 The following script fails even though the “pkonlytest” table is empty since we just created it… >>>>>>>>>>>>>>>>>>>>>>>>>> BEGIN SCRIPT CREATE TABLE pkonlytest ( pkid text PRIMARY KEY ); CREATE OR REPLACE FUNCTION createpkrecord(INOUT pkvalue text, OUT col1 boolean, OUT col2 boolean) RETURNS record AS $$ BEGIN INSERT INTO pkonlytest (pkid) VALUES (pkvalue); col1 = true; col2 = false; END; $$ LANGUAGE 'plpgsql'; SELECT ( createpkrecord('1') ).*; SQL Error: ERROR: duplicate key value violates unique constraint "pkonlytest_pkey" DETAIL: Key (pkid)=(1) already exists. CONTEXT: SQL statement "INSERT INTO pkonlytest (pkid) VALUES (pkvalue)" PL/pgSQL function "createpkrecord" line 2 at SQL statement >>>>>>>>>>>>>>>>>>END SCRIPT If you call the function without the column expansion (and required parentheses) it work just fine. SELECT createpkrecord(‘1’); There is a workaround… SELECT (func.result).* FROM ( SELECT createpkrecord('4') as result ) func David J. |
