On 29/10/11 05:59, Merlin Moncure wrote:
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
CTE: Common Table Expression
as above
WITH foo AS (...)
the temporary
table 'foo' is created once from the given
expression, and is
common to the following select and any nested sub selects.
see '7.8. WITH Queries (Common Table Expressions)' in the manual for 9.1.1.