Beth,
> However, we are in the midst of porting a mysql database to pgsql. The perl > application that is tied to this mysql database heavily utilizes > last_insert_id. In an effort to save the perl developers the pain of having > to go in and modify a bunch of their SQL to call out to seq.currval, I > thought I would attempt to mimic this function. > > Perhaps there is another way to approach this than writing this type of > function? Not really. CURRVAL requires a sequence name as a parameter because, in Postgres, one can have more than one sequence for a table. Further, in Postgres, you can update more than one table at a time using a function, trigger, or rule; how would any function know which sequence id to return? While it would be relatively easy for a function to look up what seqeunces were related to a table in the pg_* tables, you are left with the difficult task of determining what statement the user last ran. This would, I think, involve hacking MVCC to some degree. If you're serious about pursuing this, I suggest posting your question on PGSQL-HACKERS to get help with hacking MVCC to determine a connection's last action. I have no idea how easy or difficult this would be. A second possibility would be writing triggers for all tables that place a value into a temporary table that can be read back by your custom function. You may find it less work, however, to do a search-and-replace on calls to last_inser_id(). But if you do write a replacement function, please post it on TechDocs! -- -Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
