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

Re: [GENERAL] Simple problem?

From: Stan Jacobs <jacobs(at)jtek(dot)com>
To: "Ross J(dot) Reedstrom" <reedstrm(at)wallace(dot)ece(dot)rice(dot)edu>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Simple problem?
Date: 2000-03-05 04:47:41
Message-ID: 38C1E6ED.F47DC48E@jtek.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: sheila belDate: 2000-03-05 05:35:34
Subject: Re: [GENERAL] Simple problem?
Previous:From: Ross J. ReedstromDate: 2000-03-05 04:08:42
Subject: Re: [GENERAL] How to get table/DB info

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