On Fri, Oct 28, 2011 at 11:32 AM, <depstein@alliedtesting.com> wrote:
>> -----Original Message-----
>> From: Merlin Moncure [mailto:mmoncure@gmail.com]
>> Sent: Friday, October 28, 2011 8:29 PM
>> To: Dmitry Epstein
>> Cc: tgl@sss.pgh.pa.us; pgsql-general@postgresql.org; Peter Gagarinov
>> Subject: Re: [GENERAL] nextval skips values between consecutive calls
>>
>> On Fri, Oct 28, 2011 at 10:28 AM, <depstein@alliedtesting.com> wrote:
>> >> -----Original Message-----
>> >> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> >> Sent: Friday, October 28, 2011 7:22 PM
>> >> To: Dmitry Epstein
>> >> Cc: pgsql-general@postgresql.org; Peter Gagarinov
>> >> Subject: Re: [GENERAL] 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
>> >
>> > What's a good alternative in the meantime? Suppose I need to
>> > incorporate some unnests into my select, for example? (Well, I already
>> > found one alternative that seems to work, but I am not sure that's
>> > optimal.)
>>
>> Typically for guaranteed LATERAL-like behaviors you need to use a CTE.
>>
>> merlin
>
> What's a CTE?
with foo as (select generate_series(1, 1) ind)
select nextval(' test_sequence'), ind from foo;
merlin