Re: NEXT VALUE FOR <sequence>

On 3 October 2014 00:01, Thomas Munro <munro(at)ip9(dot)org> wrote:
> On 2 October 2014 14:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Thomas Munro <munro(at)ip9(dot)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