Re: SQL Help - Obtaining ID of record INSERTED - Mailing list pgsql-admin

From josh@segrestfarms.com
Subject Re: SQL Help - Obtaining ID of record INSERTED
Date
Msg-id 43032FFA.5070108@segrestfarms.com
Whole thread Raw
In response to Re: SQL Help - Obtaining ID of record INSERTED  ("Jason Minion" <jason.minion@sigler.com>)
Responses Re: SQL Help - Obtaining ID of record INSERTED
Re: SQL Help - Obtaining ID of record INSERTED
List pgsql-admin
Thank you.

You stated that the SELECT currval('orderid_id_seq') statement is a
separate query and must be treated as such.

This gives me cause for concern.  My intention is to obtain the orderid
of the order inserted at that moment, but if I am to use a separate
query it would seem there is a chance (albeit a small one) that another
order could come through before I am able to obtain the orderid.

Jason Minion wrote:

>Check out http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
>
>You'll want to use something like "SELECT currval('<name of sequence>')". The thing
>is, your orderid field in orderstemp has a sequence which it uses to grab the
>next value. The currval function called with the name of the sequence as the
>parameter will give you results as you are accustomed to. However, it is a
>separate query and must be treated as such. Note that you need to call it
>using the same database connection. And sequences are special - they always
>update and do not roll back with failed transactions.
>
>If you used default serial notation in your create table statement, your table is
>likely named "orderid_id_seq":
>
>SELECT currval('orderid_id_seq'::text);
>
>Jason
>
>-----Original Message-----
>From: pgsql-admin-owner@postgresql.org
>[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of
>josh@segrestfarms.com
>Sent: Tuesday, August 16, 2005 4:09 PM
>To: pgsql-admin@postgresql.org
>Subject: [ADMIN] SQL Help - Obtaining ID of record INSERTED
>
>
>Greetings.
>
>INSERT INTO
>    orderstemp (customerid,datecreated)
>VALUES
>    ('5443','8/16/2005 12:00PM')
>
>The table orderstemp has a unique identifier field, orderid (of type
>SERIAL).
>
>How can I obtain the orderid of the record inserted in the INSERT INTO
>statement in postgresql?
>
>MSSQL does it like this:
>
>INSERT INTO
>    orderstemp (customerid,datecreated)
>VALUES
>    ('5443','8/16/2005 12:00PM')
>SELECT @@identity as orderid
>
>but this doens't work in postgresql.
>
>Any ideas?  Thanks in advance.
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>
>
>

pgsql-admin by date:

Previous
From: "sandhya"
Date:
Subject: Re: error codes in postgresql
Next
From: Michael Fuhr
Date:
Subject: Re: SQL Help - Obtaining ID of record INSERTED