Re: oid in plpgsql trigger

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: chester c young <chestercyoung(at)yahoo(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: oid in plpgsql trigger
Date: 2002-01-19 05:19:14
Message-ID: 12215.1011417554@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

chester c young <chestercyoung(at)yahoo(dot)com> 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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Murray Prior Hobbs 2002-01-19 07:58:54 Re: pltlc and pltlcu problems
Previous Message chester c young 2002-01-19 04:14:15 oid in plpgsql trigger