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.