Thread: [HACKERS] How to get OID from INSERT in PL/PGSQL?
Is it possible to programmatically retrieve the OID of a just-inserted record in a PL/PGSQL function? Apparently, it is not currently possible in psql, but I'm hoping C programming is not required for this. If so, can someone please demonstrate how this is done? If not, can someone in the know definitely state the means by which it is currently possible to do this? 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). Other suggested approaches to skinning this cat are welcome. If PL/PGSQL can't do this, it seems rather severely limited in its usefulness for non-trivial databases. In this post, http://www.postgresql.org/mhonarc/pgsql-general/1998-07/msg00203.html Bruce Momjian says its possible for things using libpq "directly" to retrieve the oid. Does PL/PGSQL use libpq directly? This question has been asked in one form or another in a number of posts in pgsql-general and pgsql-sql, but without any definitive answers. I have experimented, scoured the mailing list archives, the postgresql PL/pgSQL documentation, and deja.com to no avail, thus my post here. So, is it possible with PL/pgSQL? How? Thanks in advance... Cheers, Ed
Ed Loehr <ELOEHR@austin.rr.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 withDEFAULT 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
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
Ed Loehr <ELOEHR@austin.rr.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 doingnewKey = 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
Tom Lane wrote:
> Ed Loehr <ELOEHR@austin.rr.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
On 1999-11-28, Ed Loehr mentioned: > Is it possible to programmatically retrieve the OID of a just-inserted > record in a PL/PGSQL function? Apparently, it is not currently > possible in psql, but I'm hoping C programming is not required for > this. For what it's worth, psql will be able to do this in the next release. It will look like this: => insert into foo values (...); => insert into bar values (:LastOid, ...); which is even marginally SQL compliant as I understand. If you are daring you can get the current snapshot and try it, but I wouldn't sign my life away on it quite yet. > Bruce Momjian says its possible for things using libpq "directly" to > retrieve the oid. Does PL/PGSQL use libpq directly? Everything(?) uses libpq more or less directly. It's just a matter of interfacing your applicaton to the OidStatus function. The above psql does just that. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden