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
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 |