Hi,
I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS.
In a PL/pgSQL function I want to insert into a table and get the OID back.
That usually works with
GET DIAGNOSTICS last_oid = RESULT_OID;
right after the insert statement.
But if the table that I insert to has a rule (or perhaps a trigger?) that
updates another table, the RESULT_OID after the insert will be 0 (zero).
Can this be fixed (I have no such problem with JDBC and getLastOID())?
Testcase:
CREATE TABLE pltest ( id BIGINT default cs_nextval('invoice_invoice_id') NOT NULL, t TEXT, primary key (id)
);
CREATE TABLE plcounter ( counter INTEGER NOT NULL
);
CREATE FUNCTION pltestfunc(integer) RETURNS BOOLEAN AS'
DECLARE lastOID OID;
BEGIN FOR i IN 1..$1 LOOP INSERT INTO pltest (t) VALUES (\'test\'); GET DIAGNOSTICS lastOID = RESULT_OID; RAISE
NOTICE\'RESULT_OID: %\', lastOID; IF lastOID <= 0 THEN RAISE EXCEPTION \'RESULT_OID is zero\'; END IF; END
LOOP;RETURN true;
END;
' LANGUAGE 'plpgsql';
-- comment out the rule and the test will work
CREATE RULE pltest_insert AS ON INSERT TO pltest DO UPDATE plcounter SET counter=counter+1;
INSERT INTO plcounter VALUES (0);
SELECT pltestfunc(10);
SELECT * FROM pltest;
DROP FUNCTION pltestfunc(integer);
DROP TABLE pltest;
Regards,
Michael
