Tom Lane wrote:
> > 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, ...);
>
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.
newOID = insert into tableWithSerialPrimaryKey(...); newKey = select serialKey from tableWithSerialPrimaryKey
whereoid =
newOID;
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". In
order to guarantee the same "session", I'm under the impression that I have
to do this either within a PL/pgSQL function for each SERIAL insert, or
maintain persistent client connections between the insert and the select on
the sequence. I think that'll work, even if it is a bit of hassle compared
to a serial insert returning the new serial value.
Thanks,
Ed Loehr
