SELECT @@IDENTITY

On 6/23/03 12:58 PM, "Robert J. Sanford, Jr." <rsanford@trefs.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

It would be nice. keep table names short, name id columns the tablename plus "id", and let PG assign sequence names, so I can write:

set sql "select currval(${tablename}_${tablename}id_seq) as lastid"

in pltcl.

In other words,

create table foobar (
foobarid serial primary key,
otherfoo varchar);

creates a sequence named foobar_foobarid_seq every time.

I guess you could even define a function called @@IDENTITY(varchar) and give it the table name... That's about as close as you are going to get.

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
Phone: (253) 798-3549
Pager: (253) 754-0002

"Robert J. Sanford, Jr." <rsanford@trefs.com> 06/23/03 10:58AM >>>

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!

rjsjr

1 - The only valid query I could find was
http://archives.postgresql.org/pgsql-general/2001-09/msg00523.php which is
unanswered.
2 - The search engine for the archives seems to ignore the "@@" in the
search string so I ended up with a LOT of information on corporate identity
and identity columns.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

I always use Steve's method (it's easier), but if you still want to do
it in the sql-server way, you can use OIDs to fetch the id of the last
INSERTed row.

I don't know which programming language you are using, but are some
examples:

*plpgsql
------------
DECLARE
myOid INTEGER;
identity INTEGER;
BEGIN
INSERT INTO <table> VALUES (<value1>, <value2>, ... <valueN>);
GET DIAGNOSTICS myOid=RESULT_OID;
SELECT INTO identity <pkfield> FROM <table> WHERE OID=myOid;
//do something with identity
END;

*php
-------
$res=pg_exec($conn, "INSERT INTO <table> VALUES (<value1>, <value2>, ...
<valueN>)");
$oid=pg_getlastoid($res);
$res=pg_exec($conn, "SELECT <pkfield> AS id FROM <table> WHERE
OID=$oid");
$row=pg_fetch_array($res, 0, PGSQL_ASSOC);
$identity=$row["id"];
//do something with $identity

*libpq
--------
PGResult *res=PQexec(con, "INSERT INTO <table> VALUES (<value1>,
<value2>, ... <valueN>)");
Oid oid=PQoidValue(res);
PQclear($res);
char buffer[255];
sprintf(buffer, "SELECT <pkfield> FROM <table> WHERE OID=%u", oid);
res=PQexec(con, buffer);
int identity=atoi(PQgetvalue(res, 0, 0));
PQclear($res);

As you see, fetching from the sequence before inserting the row seems to
be the easiest way.

On Mon, 2003-06-23 at 15:11, Steve Lane 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).

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

On 6/23/03 3:32 PM, "Robert J. Sanford, Jr." <rsanford@trefs.com> wrote:

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.

Hmm, point taken. Clearly you'd need some work in your base class. I suppose
something minimal could be that you adopt a naming convention for your
sequences such that their names can be mechanically derived from the class
names in your persistence framework (assuming an at most one-to-one
correspondence between sequences and your classes). You'd have to do a bit
more work setting up your schema since you probably couldn't use the
postgres default name for any sequence, but this might be a manageable path.

--sgl