Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] Simple problem?

From: "sheila bel" <sheilabel(at)hotmail(dot)com>
To: jacobs(at)jtek(dot)com, reedstrm(at)wallace(dot)ece(dot)rice(dot)edu
Cc: peter_e(at)gmx(dot)net, pgsql-general(at)postgreSQL(dot)org
Subject: Re: [GENERAL] Simple problem?
Date: 2000-03-05 05:35:34
Message-ID: 20000305053534.98942.qmail@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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


pgsql-general by date

Next:From: Elmar.HanekeDate: 2000-03-05 13:59:27
Subject: Re: [GENERAL] Postgresql / Threads / Scalability
Previous:From: Stan JacobsDate: 2000-03-05 04:47:41
Subject: Re: [GENERAL] Simple problem?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group