Re: nextval skips values between consecutive calls

<depstein@alliedtesting.com> writes:
> -- This is rather surprising
> select nextval(' test_sequence'), generate_series(1, 1); -- 3, 1
> select nextval(' test_sequence'), generate_series(1, 1); -- 5, 1

> Is there any explanation for why nextval skips a value in the second case?

The targetlist is evaluated twice because of the presence of the
set-returning function.  On the second pass, generate_series reports
that it's done, and so evaluation stops ... but nextval() was already
called a second time.

SRFs in SELECT targetlists are a pretty dangerous thing, with a lot of
surprising behaviors, especially if you combine them with other volatile
functions.  I recommend avoiding them.  They'll probably be deprecated
altogether as soon as we have LATERAL.

            regards, tom lane