Re: NEXT VALUE FOR

On 3 October 2014 00:01, Thomas Munro <munro@ip9.org> wrote:
> On 2 October 2014 14:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Thomas Munro <munro@ip9.org> writes:
>>> SQL:2003 introduced the function NEXT VALUE FOR <sequence>. Google
>>> tells me that at least DB2, SQL Server and a few niche databases
>>> understand it so far.  As far as I can tell there is no standardised
>>> equivalent of currval and setval (but I only have access to second
>>> hand information about the standard, like articles and the manuals of
>>> other products).
>>
>> Have you checked the archives about this?  My recollection is that one
>> reason it's not in there (aside from having to reserve "NEXT") is that
>> the standard-mandated semantics are not the same as nextval().
>
> Right, I found the problem: "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 was discussed in a thread
> from 2002 [1].
>
> So the first step would be to make a standard conforming function to transform
> the standard's syntax into.
>
> I found the text in the 20nn draft specification and it didn't seem immediately
> clear what 'statement' should mean, for example what if your statement calls
> pl/pgsql which contains further statements, and what if triggers, default
> expressions, etc are invoked?  I suppose one approach would be to use command
> IDs as the scope.  Do you think the following change would make sense?
>
> In struct SeqTableData (from sequence.c), add a member last_command_id.
> When you call the new function, let's say nextval_for_command(regclass),
> if last_command_id matches GetCommandId() then it behaves like currval_oid
> and returns last, otherwise it behaves like nextval_oid, and updates
> last_command_id to the current command ID.

Actually scratch that, it's not about statements, it's about "rows
processed by that
SQL-statement".  I will think about how that could be interpreted and
implemented...

Best regards,
Thomas Munro