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

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
cancel)********************
x
testdb=>

> 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.

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
after all.
-Peter

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden