Re: [GENERAL] Simple problem?

Thanks to everyone for their help with this-- These solutions worked
beautifully!

The only thing better than a rock-solid OpenSource database is the
support of the community behind it.... :-) Thanks again.

"Ross J. Reedstrom" wrote:
>
> On Thu, Mar 02, 2000 at 04:17:30PM +0100, 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, I was _sure_ you were around for the last few times this has come up.
> PostgreSQL's 'serial' pseudo type (convenience type?) is multiuser safe.
>
> Stan, there's a couple approaches to solving your problem:
>
> Some (such as Tom) prefer the programmatic solution: Create a sequence
> (either manually or automatically by using a 'serial' type), select
> the nextval() from the sequence yourself, then insert it, rather than
> depending on the default. However, it is also possible to do:
>
> SELECT currval('table_field_seq');
>
> Note that you're passing a string constant to the function currval,
> and that constant happens to be the name of the sequence to act on. If
> you sequence has MultiCaps (like if you used quoted "InitCaps" in table
> or field names), you'll need the double quotes, inside the single quotes:
>
> SELECT currval('"Table_Field_seq"');
>
> This is multiuser safe, since currval is defined for each SQL session
> (basically, from open of database to close of connection.) The only
> problem with it is that currval() for a sequence is undefined in
> that session until a nextval() has happened. Here's a transcript,
> demonstrating the problem, as well as the multiuser safety: (NOTICEs
> wrapped for readability)
>
> test=> create table test (s serial, t text);
> NOTICE: CREATE TABLE will create implicit sequence 'test_s_seq' for
> SERIAL column 'test.s'
> NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_s_key'
> for table 'test'
> CREATE
> test=> select currval('test_s_seq');
> ERROR: test_s_seq.currval is not yet defined in this session
> test=> insert into test (t) values ('some text');
> INSERT 883807 1
> test=> select currval('test_s_seq');
> currval
> -------
> 1
> (1 row)
>
> --------- open another session in a seperate xterm -----
>
> test=> select currval('test_s_seq');
> ERROR: test_s_seq.currval is not yet defined in this session
> test=> insert into test (t) values ('some other text');
> INSERT 883808 1
> test=> select currval('test_s_seq');
> currval
> -------
> 2
> (1 row)
>
> test=>
>
> --------- back to first session -----------------------
>
> test=> select currval('test_s_seq');
> currval
> -------
> 1
> (1 row)
>
> test=>
>
> Ross
>
> --
> Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St., Houston, TX 77005

--

Stan Jacobs
Managing Director
JTEK Interactive
jacobs(at)jtek(dot)com