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