Re: SELECT @@IDENTITY

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(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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>