how to get id of last insert on a serial type?
От | Robert J. Sanford, Jr. |
---|---|
Тема | how to get id of last insert on a serial type? |
Дата | |
Msg-id | JDEFKDKCIFCAABOIMHJGAEFMCCAA.rsanford@trefs.com обсуждение исходный текст |
Ответы |
Re: how to get id of last insert on a serial type?
|
Список | pgsql-general |
I am in the process of converting a java application from MS SQL Server over to Postgres. The application heavily abstracts interaction with the database and unfortunately appears to be making some assumptions about functionality available within SQL Server that I can't find information on duplicating inside of Postgres. The one bit that I'm currently having the largest issue with is the @@IDENTITY property. For those not familiar with SQL Server, performing "SELECT @@IDENTITY" allows a user to retrieve the primary key of the last row inserted into a table (assuming the table is using an identity column as the primary key). In order for this to work the SELECT must occur immediately after the insert on the same connection. Any other SQL statements executed on that connection will result in resetting of @@IDENTITY either to the next identity value if an insert occurs or NULL otherwise. This is somewhat restrictive but under the right conditions works exceedingly well. I've done some searching through the mail archives but the only instances of @@IDENTITY that I can find are two top-level questions that don't appear to have any answers :( Searching through the archives to find information on SERIAL types is a bit of a chore with searches regularly returning > 1000 hits with the first several hundred being non-topical. A pointer to the top level of a relevant thread would be most welcome. Reading through the documentation I am under the impression that I could write a PL/PGSQL function that would select the next value of an implicitly created sequence based on the serial datatype and then perform an insert with that selected value and then return the value from the function. But... I sincerely hope that is not the only method of doing this becuase that would mean that I have a LOT of code to rewrite. Does anyone have any joy to share with me? rjsjr
В списке pgsql-general по дате отправления: