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


Sun, 28 Nov 1999 13:45:19 -0600
Ed Loehr <ELOEHR@austin.rr.com>
[HACKERS]  How to get OID from INSERT in PL/PGSQL?
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 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

Sun, 28 Nov 1999 18:30:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] How to get OID from INSERT in PL/PGSQL? 
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 where oid =
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


Sun, 28 Nov 1999 23:22:03 -0600
Ed Loehr <ELOEHR@austin.rr.com>
Re: [HACKERS] How to get OID from INSERT in PL/PGSQL?
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 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

Mon, 29 Nov 1999 00:41:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] How to get OID from INSERT in PL/PGSQL? 
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



Mon, 29 Nov 1999 00:45:17 -0600
Ed Loehr <ELOEHR@austin.rr.com>
Re: [HACKERS] How to get OID from INSERT in PL/PGSQL?
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



Tue, 30 Nov 1999 01:03:50 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS]  How to get OID from INSERT in PL/PGSQL?