Re: SQL-question: returning the id of an insert querry

On Wed, 12 Nov 2003, Scott Chapman wrote:

> On Wednesday 12 November 2003 12:31, Doug McNaught wrote:
> > Scott Chapman <scott_list(at)mischko(dot)com> writes:
> > > On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
> > > > Scott Chapman <scott_list(at)mischko(dot)com> writes:
> > > > > It would be nice if PostgreSQL could return the primary key it
> > > > > inserted with but that may not be a fool-proof solution either.
> > > > > Is there a nice way to handle this situation?
> > > >
> > > > Write a database function that inserts the record and returns the
> > > > primary key value? That's probably the best way to insulate your
> > > > app from the database structure...
> > >
> > > The function still has to know which sequence to pull from doesn't
> > > it?
> >
> > Yes. It's theoretically possible to derive that information if you
> > have enough system-tables-fu, but since the function knows which
> > table it's inserting into, it's not hard to put the proper sequence
> > name in as well.
> >
> > > I don't know much about triggers/functions in PG. Is it possible
> > > to have a function that intercepts the information AFTER the
> > > sequence value is added as the new primary key and then return it?
> > > This would enable the use of a more generic function.
> >
> > Sure, in the function you would basically do (I forget the exact
> > pl/pgsql syntax):
> >
> > INSERT INTO foo VALUES (...);
> > SELECT currval('the_pk_sequence') INTO pk;
> > RETURN pk;
> >
> > Doesn't remove the need to know or derive the proper sequence name.
> > There is no "what primary key did I just insert" built into PG. And
> > you will need a separate function for each table.
> >
> > But this way the DB knowledge resides in the DB and you just have a
> > nice clean API for inserting data from the clients. The schema can
> > change and the API will (homefully) remain the same...
>
> What's the process to suggest changes to PG along these lines? Say, a
> proposal to make it configurable for a user to have a INSERT return the
> primary key that it just inserted rather than what it returns now?

I wouldn't suggest changing current bevaiour (i.e. the number of rows
inserted is probably a SQL SPEC thing) but to have each serial column in a
table be addressable so you'd just do:

select tablename.fieldname.currval;

and you'd get the currval back for the serial. note that if the serial
value was implemented by hand like:

create table test (id int primary key default
'myseq'::text||nextval('seqname')::text);

that currval or the equivalent would actually give back the key inserted,
'myseqx' where x was the sequence number.

The folks are -hackers are always willing to listen to a good idea, but
they've got plenty on their plates, so this kind of thing needs to be at
the least thought out well enough so they won't have lots of
implementation problems with the plan.

It might also be possible to have the serial type create a plsql function
that has the name tablename_fieldname_currval() and returns the last
currval('seqname') with a simple wrapper. That solution would be fairly
easy to implement, and would be quite useful.