Re: [HACKERS] Getting OID in psql of recent insert - Mailing list pgsql-hackers

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> In writing the book, I see the serious limitation that there is no way
> in psql to access the most recently inserted oid.  Without it, there
> seems to be no way to use the oid value as a foreign key in another
> table.

> Should I add a function to return the most recently assigned oid to the
> backend, or is there a better way?

I'm not sure why, but a backend-side function seems like the wrong way
to approach it.  I guess I'm worried that the state would be too
volatile on the backend side.  (Example: if you use the hypothetical
lastoid() function in an SQL query that causes triggers or rules to
be invoked behind your back, those triggers/rules could do new inserts.
Will lastoid() still return the "right" value by the time it gets
executed?)

It'd certainly be easy enough for psql to save off the OID anytime it
gets an "INSERT nnn" command response.  The missing link is to invent
a way for a psql script to access that value and insert it into
subsequent SQL commands.

If you want to attack this, I'd suggest thinking a little larger than
just the last-OID problem.  I'd like to be able to save off both
insertion OIDs and values extracted by SELECTs into named variables
of some sort, and then insert those values into as many later commands
as I want.  Right now there's no way to do any such thing in a psql
script; you have to move up a level of difficulty into ecpg or pgtcl
or even C code if your application needs this.  Plain psql scripts
would become substantially more powerful if psql had a capability
like this.

OTOH: we shouldn't ask psql to do everything under the sun.  I'd
certainly think that it'd be unreasonable to try to do conditional
evaluation or looping in psql scripts, for instance.  Maybe the right
answer is to teach people a little bit about using honest-to-goodness
scripting languages when their applications reach this level of
complexity.  How much daylight is there between needing script
variables and needing control flow, do you think?
        regards, tom lane

PS: not relevant to your main point, but to your example: I think it's
a real bad idea to teach people to use OIDs as foreign keys.  That'll
create all kinds of trouble when it comes time to dump/reload their
database.  Better to tell them to use SERIAL columns as keys.  Not so
incidentally, we have currval() already...