Re: [HACKERS] How to get OID from INSERT in PL/PGSQL?

Ed Loehr <ELOEHR(at)austin(dot)rr(dot)com> writes:
> Is it possible to programmatically retrieve the OID of a just-inserted
> record in a PL/PGSQL function?

It seems to me that an AFTER INSERT ROW trigger, as well as any kind of
UPDATE or DELETE ROW trigger, ought to have access to the OID of the
row it is fired for. But if it's there in PL/PGSQL, I'm missing it.

I think you could get at the OID from a C-coded trigger procedure, but
I agree that that's more trouble than it's worth.

> Why would someone want to do this? Because it is the only way I know
> of to definitively retrieve a newly-generated serial value for use as
> the primary/foreign key (a *very* common RDBMS practice).

Actually, using OID as a key is deprecated, because dumping and
reloading a DB that contains references to rows by their OIDs is a
risky proposition. I'd suggest using a SERIAL column instead.
SERIAL is basically syntactic sugar for an int4 column with
DEFAULT nextval('associatedSequenceObject')
and this operation generates serial IDs just fine. Or, if you want to
prevent the user from trying to insert a key at random, don't use the
nextval() as a default; instead generate the key value inside the
BEFORE INSERT trigger procedure, overriding whatever the user might
have tried to supply:

new.keycol = select nextval('sequenceObject');
insert into otherTable values(new.keycol, ...);

Anyway, the point is that nextval() is considerably more flexible than
relying solely on the OID sequence generator.

regards, tom lane