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