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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
