Re: NEXT VALUE FOR sequence

On Tue, Feb 20, 2018 at 8:39 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> Tom Lane wrote:
>> Laurenz Albe <laurenz.albe@cybertec.at> writes:
>> > The SQL standard has the expression "NEXT VALUE FOR asequence" to do
>> > what we traditionally do with "nextval('asequence')".
>> > This is an attempt to implement this on top of the recently introduced
>> > NextValueExpr node.
>>
>> This has been proposed repeatedly, and rejected repeatedly, because in
>> fact the standard's semantics for NEXT VALUE FOR are *not* like nextval().
>> See SQL:2011 4.22.2 "Operations involving sequence generators":
>>
>>     If there are multiple instances of <next value expression>s specifying
>>     the same sequence generator within a single SQL-statement, all those
>>     instances return the same value for a given row processed by that
>>     SQL-statement.
>>
>> This is not terribly exact --- what is a "processed row" in a join query,
>> for instance?  But it's certainly not supposed to act like independent
>> executions of nextval() or NextValueExpr would.  Pending somebody doing
>> the legwork to produce something that at least arguably conforms to the
>> spec's semantics, we've left the syntax unimplemented.
>
> Would it be reasonable to say that any two NextValueExpr in the same
> target list are "in one row"?

I think, "processed row" thing gets pretty complicated. Consider
simple case. What happens when NextValueExpr appears in one of the
conditions and that row gets eliminated, do we consider that as a
processed row and increment the NextValueExpr or do not increment it?

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company