Re: [SQL] currval() within one statement

2008-01-22 Thread Richard Huxton
silly_sad wrote: id default value is always set by the trigger before insert on each table for each row. The particular subproblem is to insert one record into ttt1 and then insert corresponding record into ttt, ___This is the place to use currval. I'd be tempted to turn it the other way ar

Re: [SQL] currval() within one statement

2008-01-22 Thread hubert depesz lubaczewski
On Tue, Jan 22, 2008 at 10:16:30AM +0300, silly_sad wrote: > INSERT INTO ttt (a,b) SELECT currval('ttt_id_seq'), 'const' FROM ttt2; > Where the trigger before insert on ttt is defined and this trigger calls > nextval('ttt_id_seq'). > I was surprised having different values of currval() in ttt.a >

Re: [SQL] currval() within one statement

2008-01-22 Thread silly_sad
Richard Huxton wrote: sad wrote: Richard Huxton wrote: Then this is the question on the execution order of the statement INSERT...SELECT... You'll want "Overview of PostgreSQL internals" then http://www.postgresql.org/docs/8.2/static/overview.html What do you think should happen? I had

Re: [SQL] currval() within one statement

2008-01-22 Thread Richard Huxton
sad wrote: Richard Huxton wrote: Then this is the question on the execution order of the statement INSERT...SELECT... You'll want "Overview of PostgreSQL internals" then http://www.postgresql.org/docs/8.2/static/overview.html What do you think should happen? I had expected all the currva

Re: [SQL] currval() within one statement

2008-01-22 Thread Guillaume Lelarge
sad wrote: Guillaume Lelarge wrote: sad wrote: A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only note that

Re: [SQL] currval() within one statement

2008-01-22 Thread sad
Richard Huxton wrote: sad wrote: A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only note that i still want t

Re: [SQL] currval() within one statement

2008-01-22 Thread Guillaume Lelarge
sad wrote: A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only note that i still want to discuss the titled pr

Re: [SQL] currval() within one statement

2008-01-22 Thread Richard Huxton
sad wrote: A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only note that i still want to discuss the titled pr

Re: [SQL] currval() within one statement

2008-01-21 Thread sad
A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only note that i still want to discuss the titled problem or to

Re: [SQL] currval() within one statement

2008-01-21 Thread A. Kretschmer
am Tue, dem 22.01.2008, um 10:16:30 +0300 mailte silly_sad folgendes: > Helo > > is it expected that the currval() changes its value between calls within > one statement ? > > Look the following call: > > INSERT INTO ttt (a,b) SELECT currval('ttt_id_seq'), 'const' FROM ttt2; This fails if you

[SQL] currval() within one statement

2008-01-21 Thread silly_sad
Helo is it expected that the currval() changes its value between calls within one statement ? Look the following call: INSERT INTO ttt (a,b) SELECT currval('ttt_id_seq'), 'const' FROM ttt2; Where the trigger before insert on ttt is defined and this trigger calls nextval('ttt_id_seq'). I