Re: how to get id of last insert on a serial type?

Two quick notes/questions...

1) That would still require me to write a function for each of
   inserts to make sure that the value came back. I can't just
   do a "SELECT @@IDENTITY" and get back the last identity that
   was inserted, I have to know what sequence was used. The
   current code base that is returning the @@IDENTITY doesn't
   know anything about what was actually inserted, just that
   something was.

2) Can I lock the sequence to make sure that another INSERT
   doesn't occur before I select the currval() of the sequence?

rjsjr

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Saturday, May 18, 2002 5:52 PM
> To: Robert J. Sanford, Jr.
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] how to get id of last insert on a
> serial type?
>
>
> "Robert J. Sanford, Jr." <rsanford@trefs.com> writes:
> > 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)
>
> Use a serial column as the primary key, and then @@IDENTITY can be
> implemented as currval() on the associated sequence.  This is more
> flexible than what you describe for SQL Server, because the
currval
> can be retrieved at any later time in the same session --- as long
> as you don't do another insert into the same table, it's
> still good.
>
>             regards, tom lane
>