Re: [GENERAL] Simple problem?
От | Stan Jacobs |
---|---|
Тема | Re: [GENERAL] Simple problem? |
Дата | |
Msg-id | 38C1E6ED.F47DC48E@jtek.com обсуждение исходный текст |
Ответ на | Simple problem? (Stan Jacobs <jacobs@jtek.com>) |
Список | 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@rice.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@jtek.com
В списке pgsql-general по дате отправления: