pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
oid in plpgsql trigger
2+ messages / 2 participants
[nested] [flat]

* oid in plpgsql trigger
@ 2002-01-19 04:14  chester c young <chestercyoung@yahoo.com>
  0 siblings, 1 reply; 2+ messages in thread

From: chester c young @ 2002-01-19 04:14 UTC (permalink / raw)
  To: pgsql-sql

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.

cc young

"Beauty begets beauty, and ugly ugly."  (paraphrase) Harry Truman

__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: oid in plpgsql trigger
@ 2002-01-19 05:19  Tom Lane <tgl@sss.pgh.pa.us>
  parent: chester c young <chestercyoung@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2002-01-19 05:19 UTC (permalink / raw)
  To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql

chester c young <chestercyoung@yahoo.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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-01-19 05:19 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-19 04:14 oid in plpgsql trigger chester c young <chestercyoung@yahoo.com>
2002-01-19 05:19 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox