On 1999-11-18, Tom Lane mentioned:
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.
Okay, I guess I'm way ahead of everyone here. It is in fact only a matter
of adding a few lines to save the oid in a variable, and all the
infrastructure for doing this is already present. In fact, I was going to
do this in the next few days.
testdb=> \set singlestep on
testdb=> \set sql_interpol '#'
testdb=> \set foo 'pg_class'
testdb=> select * from #foo#;
***(Single step mode: Verify query)**************
QUERY: select * from pg_class
***(press return to proceed or enter x and return to
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
Hmm, saving the SELECT results in a variable sounds like a great
idea. I'll work on that. But in general, all the framework for this sort
of thing is already there as you see.
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
I actually had (simple) conditional expressions on my list, but loops are
not possible in the current design. Since I just redesigned it, I am quite
hesitant to changing the design again.
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?
Good question. It has been bothering me all along. The best answer to this
is probably an interactive interpreter of some procedural language we
offer. (I recall Oracle has their frontend that way.) Adding any more
complex functionality to psql will probably cripple it beyond recognition.
You can only go so far with hand-written parsers acting on poorly
specified rules consisting of tons of backslashes. :)
Anyway, good to see that all this "thinking big" might have had a point