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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Saturday, May 18, 2002 5:52 PM
> To: Robert J. Sanford, Jr.
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] how to get id of last insert on a
> serial type?
>
>
> "Robert J. Sanford, Jr." <rsanford(at)trefs(dot)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
>