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

Tom Lane wrote:

> 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?

Because it's not inside a trigger proc, but rather a simple PL/pgSQL function,
so NEW is not available.

> > 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.

That is what I ended up doing, and it works (not too painful). Thanks.

Cheers,
Ed Loehr