Re: nextval skips values between consecutive calls

> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
> Sent: Friday, October 28, 2011 8:29 PM
> To: Dmitry Epstein
> Cc: tgl(at)sss(dot)pgh(dot)pa(dot)us; pgsql-general(at)postgresql(dot)org; Peter Gagarinov
> Subject: Re: [GENERAL] nextval skips values between consecutive calls
>
> On Fri, Oct 28, 2011 at 10:28 AM, <depstein(at)alliedtesting(dot)com> wrote:
> >> -----Original Message-----
> >> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> >> Sent: Friday, October 28, 2011 7:22 PM
> >> To: Dmitry Epstein
> >> Cc: pgsql-general(at)postgresql(dot)org; Peter Gagarinov
> >> Subject: Re: [GENERAL] nextval skips values between consecutive calls
> >>
> >> <depstein(at)alliedtesting(dot)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?