Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0J5JBV35805 for ; Sat, 19 Jan 2002 00:19:11 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g0J5JF312218; Sat, 19 Jan 2002 00:19:15 -0500 (EST) To: chester c young cc: pgsql-sql@postgresql.org Subject: Re: oid in plpgsql trigger In-reply-to: <20020119041415.23759.qmail@web12705.mail.yahoo.com> References: <20020119041415.23759.qmail@web12705.mail.yahoo.com> Comments: In-reply-to chester c young message dated "Fri, 18 Jan 2002 20:14:15 -0800" Date: Sat, 19 Jan 2002 00:19:14 -0500 Message-ID: <12215.1011417554@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200201/231 X-Sequence-Number: 6315 chester c young writes: > Is there any way to get the current record's oid in a plpgsql trigger? > In a before insert trigger the oid might not make any sense if the oid > has not yet have been assigned, but it makes sense elsewhere. Indeed the OID hasn't been assigned yet in a BEFORE INSERT trigger. 7.1 plpgsql doesn't have support for accessing any system columns, OID or the others. But it works about like you'd expect in 7.2: regression=# CREATE FUNCTION show_oid() RETURNS OPAQUE AS ' regression'# BEGIN regression'# RAISE NOTICE ''oid is %'', NEW.oid; regression'# RETURN NEW; regression'# END;' LANGUAGE 'plpgsql'; CREATE regression=# create table foo (f1 int); CREATE regression=# CREATE TRIGGER before_oid BEFORE INSERT ON foo regression-# FOR EACH ROW EXECUTE PROCEDURE show_oid(); CREATE regression=# insert into foo values(11); NOTICE: oid is 0 INSERT 139803 1 regression=# CREATE TRIGGER after_oid AFTER INSERT ON foo regression-# FOR EACH ROW EXECUTE PROCEDURE show_oid(); CREATE regression=# insert into foo values(22); NOTICE: oid is 0 NOTICE: oid is 139805 INSERT 139805 1 What might surprise you is OID's not set yet in a BEFORE UPDATE trigger, either: regression=# CREATE TRIGGER before_update_oid BEFORE UPDATE ON foo regression-# FOR EACH ROW EXECUTE PROCEDURE show_oid(); CREATE regression=# update foo set f1 = f1 + 1; NOTICE: oid is 0 NOTICE: oid is 0 UPDATE 2 regression=# CREATE TRIGGER after_update_oid AFTER UPDATE ON foo regression-# FOR EACH ROW EXECUTE PROCEDURE show_oid(); CREATE regression=# update foo set f1 = f1 + 1; NOTICE: oid is 0 NOTICE: oid is 0 NOTICE: oid is 139803 NOTICE: oid is 139805 UPDATE 2 However the problem here is we are looking at NEW.oid, which is the row-under-construction and behaves much like a row being inserted. If we looked at OLD.oid, it'd be set correctly in both UPDATE triggers. Likewise, in a DELETE trigger, OLD.oid is valid either BEFORE or AFTER. regards, tom lane