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?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Bug in PL/pgSQL GET DIAGNOSTICS?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: making use of large TLB pages
Следующее
От: Bruce Momjian
Дата:
Сообщение: CVS checkout errors