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

Ed Loehr <ELOEHR(at)austin(dot)rr(dot)com> writes:
> The scenario I unsuccessfully attempted to communicate is one in which the
> OID is used not as a key but rather as the intermediate link to get to the
> newly generated SERIAL value, which *is* a primary/foreign key. In other
> words, the OID is used to identify the newly-inserted row so that I can
> query it to find out the newly generated SERIAL value just after an insert.

but ... but ... if you are using a trigger procedure then you can just
read the SERIAL column's value out of the new tuple! Why bother with
a select on OID?

> newOID = insert into tableWithSerialPrimaryKey(...);
> newKey = select serialKey from tableWithSerialPrimaryKey where oid =
> newOID;

If you need to do it like that (ie, not inside a trigger procedure for
tableWithSerialPrimaryKey), consider doing
newKey = nextval('sequenceObjectForTableWithSerialPrimaryKey');
insert into tableWithSerialPrimaryKey(newKey, other-fields);
ie, do the nextval() explicitly and then insert the value, rather than
relying on the default-value expression for the key column.

> I'm told I can safely retrieve the last SERIAL value via currval() on
> the implicit primary key serial sequence if done within the same
> "session".

I don't trust currval a whole lot either... it's OK in simple cases, but
if you have trigger procedures and rules firing all over the place then
you can't always be sure that only one row has gotten inserted... so the
currval might not correspond to the row you were interested in.

nextval() *will* give you a distinct value for each time you call it,
and then you just have to propagate that value to the places it should
go.

regards, tom lane