Thread: 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! 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.
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,
soI 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
closeas 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:
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:
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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Attachment
> > 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