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