Re: sequence incrementing twice

On Wed, 14 Jan 2004, ezra epstein wrote:

"dan" <hassanbensober@lycos.com> wrote in message
news:9aa190bb.0401130958.6a3cfac5@posting.google.com...

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