By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,834 Members | 2,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,834 IT Pros & Developers. It's quick & easy.

sequence incrementing twice

P: n/a
dan
I have 2 tables, tab1 ( integer incremented sequence , col2, col3 )
and tab2 ( integer from tab1, col4, col5 ). When I call this function
to add a record to each table:

LOOP
select nextval('sequence') into id_car; // for looping

INSERT INTO tab1
VALUES (default, col2, col3);

INSERT INTO tab2
VALUES (currval('sequence'), col3, col4);
END LOOP

my sequence gets incremented twice. If I use currval in the select,
then it is not yet defined. I'd love to have the sequence increment
only once.
Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"dan" <ha************@lycos.com> wrote in message
news:9a**************************@posting.google.c om...
I have 2 tables, tab1 ( integer incremented sequence , col2, col3 )
and tab2 ( integer from tab1, col4, col5 ). When I call this function
to add a record to each table:

LOOP
select nextval('sequence') into id_car; // for looping

INSERT INTO tab1
VALUES (default, col2, col3);

INSERT INTO tab2
VALUES (currval('sequence'), col3, col4);
END LOOP

my sequence gets incremented twice. If I use currval in the select,
then it is not yet defined. I'd love to have the sequence increment
only once.


First off, you could instead do:

INSERT INTO tab2
VALUES (id_car, col3, col4);

Though that won't change the double-increment. For that you need to be sure
the sequence isn't being invoked somewhere else? E.g., a trigger or a
default value or.... ??? (Or, of course, another session...)

== Ezra Epstein
Nov 22 '05 #2

P: n/a
On Wed, 14 Jan 2004, ezra epstein wrote:

"dan" <ha************@lycos.com> wrote in message
news:9a**************************@posting.google.c om...
I have 2 tables, tab1 ( integer incremented sequence , col2, col3 )
and tab2 ( integer from tab1, col4, col5 ). When I call this function
to add a record to each table:

LOOP
select nextval('sequence') into id_car; // for looping

INSERT INTO tab1
VALUES (default, col2, col3);

INSERT INTO tab2
VALUES (currval('sequence'), col3, col4);
END LOOP

my sequence gets incremented twice. If I use currval in the select,
then it is not yet defined. I'd love to have the sequence increment
only once.
First off, you could instead do:

INSERT INTO tab2
VALUES (id_car, col3, col4);

Though that won't change the double-increment.


That's because that whole sequence of operations is wrong. A far more sensible
function would look like:
FOR whatever LOOP
SELECT INTO myvar nextval(''sequencename'');

INSERT INTO tab1 (colname1, colname2, colname3)
VALUES (myvar, col2, col3);

INSERT INTO tab2 (col1name2, col2name2, col3name2)
VALUES (myvar, col3, col4);
END LOOP

If you are happy with the assumption that the default in your insert into tab1
statement does a nextval on the sequence than you can move your select (with
the correct syntax of course) after the first insert and use the variable name
as in Dan's answer or just forget it altogether and just do the insert using
currval as in your original sequence.
For that you need to be sure
the sequence isn't being invoked somewhere else? E.g., a trigger or a
default value or.... ??? (Or, of course, another session...)


In this case I assume the double increment is due to the default keyword on the
insert into tab1.
--
Nigel Andrews

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.