Bug in PL/pgSQL GET DIAGNOSTICS?

From: "Michael Paesold" <mpaesold(at)gmx(dot)at>
To: "PostgreSQL Hackers Mailing List" <pgsql-hackers(at)postgresql(dot)org>
Subject: Bug in PL/pgSQL GET DIAGNOSTICS?
Date: 2002-09-25 18:08:57
Message-ID: 025d01c264be$9f83bd10$4201a8c0@beeblebrox
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2002-09-25 18:34:49 CVS checkout errors
Previous Message Tom Lane 2002-09-25 17:30:19 Re: making use of large TLB pages