Function Column Expansion Causes Inserts To Fail

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.