Scott Chapman <scott_list@mischko.com> writes:
> On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
> > Scott Chapman <scott_list@mischko.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
