Re: oid in plpgsql trigger

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