Re: SELECT @@IDENTITY

> > Okay, I did a quick search through both the general
> > and SQL lists(1,2) trying to determine if there is
> > a PostgreSQL construct equivalent to Microsoft SQL
> > Server's "SELECT @@IDENTITY". After performing an
> > insert the database caches the last sequence number
> > for each connection and the select retrieves that
> > value. Thus if connection A and connection B each
> > inserted into the same table they would each know
> > what the value of the sequence was immediately
> > after their insert.
> >
> > Is there any direct support for this? I know that
> > I can manually code to select the nextval of a
> > sequence but the syntactic sugar of SELECT
> > @@IDENTITY is really nice.
> >
> > Many thanks!
>
> Hi Robert:
>
> You'll want to consult the postgres docs about
> sequences. The function you're looking for is called
> currval(), and selects just what you want -- the most
> recent value delivered for a sequence over a given
> connection. (I've always been curious about how well
> this behaves, or not, with connection pooling).

From the user docs I read...

   currval(text)
   "Return the value most recently obtained by nextval
   for this sequence in the current session. (An error
   is reported if nextval has never been called for
   this sequence in this session.) Notice that because
   this is returning a session-local value, it gives a
   predictable answer even if other sessions are
   executing nextval meanwhile."

While that does mostly what I want it doesn't do it completely. With this I
am forced to specify the name of the sequence that I want the currval for.
What I really, really want is to not have to worry about it. The reason
being that I have a persistence framework that I want to port from SQL
Server to PostgreSQL. The framework base class object knows it is working on
an object but doesn't really know what object it is. Under SQL Server it
does an insert using a static method call and then sets the internal unique
key based on the call to SELECT @@IDENTITY. It's simple and nice. Pretty
much everything else works with minimal mods. With this I'll have to
actually do some real work to make the conversion.

rjsjr