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

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

-Doug