Re: [GENERAL] Simple problem?

I just wanted to add to this my gratitude to all who have
helped me too. I'm happy with my choice of PostgreSQL
mainly because of the support I was given. Thanks everyone :)
-Sheila

>
>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
>
>************
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com