Re: NEXT VALUE FOR

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.

BTW there was also a problem with the handling of quoted identifiers (ie case
folding etc), which is fixed in the v2 patch, attached.

[1] http://www.postgresql.org/message-id/j9o9uugl8aeq5bn8cbvcttnoc1f263lt8d@4ax.com

Best regards,
Thomas Munro