Re: Synthesize support for Statement.getGeneratedKeys()?

[Dave Cramer <pg(at)fastcrypt(dot)com>]

On 23-Jan-07, at 2:00 AM, Ken Johanson wrote:

> > > > > My real question is, what about the case where multiple VALUES  
> > > > > are inserted; if I have 3 values should I call the sequence 3  
> > > > > times? What is the most efficient was to do that? (Can I do it  
> > > > > in a single query?)
> > > >
> > > > I don't think you should use "currval" or "nextval" at all. A  
> > > > general solution in the JDBC driver should even work in the case  
> > > > of triggers that interfere with the value of a sequence. Or  
> > > > which might change the value actually inserted into the table.  
> > > > Just think of an insert trigger that uses a sequence for a  
> > > > second time.
> > > >
> > > > There is only one way to reliably get the database generated  
> > > > values: the RETURNING clause.
> > > >
> > > > So my basic suggestion was to rewrite a query written as:
> > > > "INSERT INTO tab VALUES (...)"
> > > > into
> > > > "INSERT INTO tab VALUES (...) RETURNING x"
> > > >
> > > > With x being either (a) what the user specified using the Java  
> > > > API (i.e. any column names) or (b) the primary key column(s) (or  
> > > > other columns having a "DEFAULT currval(...)").
> > > > The second case (b) I would leave for later, since it requires  
> > > > parsing the query and finding the table which will be inserted  
> > > > into. And you would have to use database meta data to find the  
> > > > columns to return.
>
>
> I think that, given everyone's input (including Vit's, thanks) and  
> mention of possible variation on query, possible need to parse for  
> table/column names, and/or need to call database metadata / or  
> result set metadata (to get keys?) (which require another trip to  
> the server?)... this might be out of my league. Well, even if I did  
> get it working, it likely would not work in every case (triggers  
> etc), and would eventually be replaced when V4 protocol comes around.
>
> Unless one of the PG folks can prescribe, in exact terms, the very  
> best way to execute this (after which I would build out the actual  
> patch)... then I may have to bow out of this (it's complex / error  
> prone enough to frighten lil'ol me, and time is a bit short on my  
> end too I'm afraid).

If we can implement the one which does specify the keys that would be  
useful. Statement.getGeneratedKeys( columns )
> Perhaps it's better for everyone if we lobby to have the backend/ 
> protocol to add this natively (as you all have suggested). So..
>
> Does anyone know if the actual server core natively has the ability  
> to build created-keys resultsets (without having to modify the  
> query / RETURNS), or is this truly a protocl bottleneck?...
I don't know for absolute certainty but I highly suspect that it  
does, since it can return the columns returned via insert returning

Dave
> Thanks,
> Ken