Scott Chapman <[EMAIL PROTECTED]> writes: > On Wednesday 12 November 2003 11:29, Doug McNaught wrote: > > Scott Chapman <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match