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
