Re: SELECT @@IDENTITY

On 6/23/03 12:58 PM, "Robert J. Sanford, Jr." <rsanford(at)trefs(dot)com> wrote:

> 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).

-- sgl