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

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?

Scott