Bug in PL/pgSQL GET DIAGNOSTICS?
От | Michael Paesold |
---|---|
Тема | Bug in PL/pgSQL GET DIAGNOSTICS? |
Дата | |
Msg-id | 025d01c264be$9f83bd10$4201a8c0@beeblebrox обсуждение исходный текст |
Ответ на | Re: PGXLOG variable worthwhile? (Bruce Momjian <pgman@candle.pha.pa.us>) |
Ответы |
Re: Bug in PL/pgSQL GET DIAGNOSTICS?
Re: Bug in PL/pgSQL GET DIAGNOSTICS? |
Список | 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
В списке pgsql-hackers по дате отправления: