You can do a SELECT curval('sequence_name'); to get the value you were just
assigned without worrying about other people updating, as curval gets the
current value within the current transaction (or within the current
session, either way, it works.)
Alternately, you can get the nextval before doing either of the inserts
with a select statement, and then use the returned value in you SQL.
Ex:
$OrderID=SELECT nextval('seq_order_id') AS order_id;
INSERT INTO orders (order_id, blah, blah) VALUES ($OrderID, blah, blah);
INSERT INTO items (order_id, blah, blah) VALUES ($OrderID, blah, blah);
At 11:17 AM 3/2/00, Peter Eisentraut wrote:
>On Wed, 1 Mar 2000, Stan Jacobs wrote:
>
> > I must be confusing my syntax somehow, because I'm having trouble doing a
> > simple update with returned id... As in.... I have two tables: an order
> > table and an orderitems table. I need to write the order record, then use
> > the order_id from the order record in the insert to the orderitems record.
>
>I assume you're using serial columns here. Table schemas always help ...
>
> > 1. Can I use a transaction begin/end for the entire transaction if I need
> > to get the order_id in between the two writes?
>
>Sure.
>
> > 2. How do I get the order_id from the record I just wrote?
>
>You have to select it back out. A select on max(order_id) might do, but
>you might get caught if someone else does updates on your table as well. I
>am personally not so fond of serial columns because of this problem.
>
>--
>Peter Eisentraut Sernanders väg 10:115
>peter_e@gmx.net 75262 Uppsala
>http://yi.org/peter-e/ Sweden
>
>
>************
************