Thread: support for NEXT VALUE FOR expression
Here is a patch for implementing the NEXT VALUE FOR expression. This is the SQL-standard conforming version of our nextval() function, and it's also used by Oracle, MS SQL, DB2. Example: SELECT NEXT VALUE FOR foo_seq; The second patch changes the serial column to use this new expression for its generated default values. This doesn't make an external difference except perhaps that the generated expression looks less weird to the user. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: > Here is a patch for implementing the NEXT VALUE FOR expression. This is > the SQL-standard conforming version of our nextval() function, and it's > also used by Oracle, MS SQL, DB2. Example: We discussed this before and concluded that NEXT VALUE FOR is in fact *not* an exact semantic equivalent of nextval(): https://www.postgresql.org/message-id/14790.1083955136%40sss.pgh.pa.us I remain of the opinion that using spec-compliant syntax for non-spec-compliant behavior isn't a great advance. regards, tom lane
On Wed, Aug 17, 2016 at 3:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: >> Here is a patch for implementing the NEXT VALUE FOR expression. This is >> the SQL-standard conforming version of our nextval() function, and it's >> also used by Oracle, MS SQL, DB2. Example: > > We discussed this before and concluded that NEXT VALUE FOR is in fact > *not* an exact semantic equivalent of nextval(): > > https://www.postgresql.org/message-id/14790.1083955136%40sss.pgh.pa.us And also again 10 years later when I proposed it :-) https://www.postgresql.org/message-id/flat/CADLWmXUY2oo4XObQWF3yPUSK%3D5uEiSV%3DeTyLrnu%3DRzteOy%2B3Lg%40mail.gmail.com > I remain of the opinion that using spec-compliant syntax for > non-spec-compliant behavior isn't a great advance. -- Thomas Munro http://www.enterprisedb.com
Thomas Munro <thomas.munro@enterprisedb.com> writes: > On Wed, Aug 17, 2016 at 3:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> We discussed this before and concluded that NEXT VALUE FOR is in fact >> *not* an exact semantic equivalent of nextval(): >> https://www.postgresql.org/message-id/14790.1083955136%40sss.pgh.pa.us > And also again 10 years later when I proposed it :-) > https://www.postgresql.org/message-id/flat/CADLWmXUY2oo4XObQWF3yPUSK%3D5uEiSV%3DeTyLrnu%3DRzteOy%2B3Lg%40mail.gmail.com And that links to yet another thread, from 2002 ;-) The 2004 thread does contain some speculation about how to implement the spec's semantics. It seems like the first problem is nailing down what is meant by "once per row", particularly in cases with nested execution contexts. regards, tom lane
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> Here is a patch for implementing the NEXT VALUE FOR expression. This is
> the SQL-standard conforming version of our nextval() function, and it's
> also used by Oracle, MS SQL, DB2.
BTW, several of the earlier threads complained of needing to make NEXT
a fully-reserved word in order to get this to parse without shift/reduce
conflicts. How did you avoid that? I notice that your patch puts the
new production into c_expr not func_expr_common_subexpr which would
seem like the obvious place. If that is what's making the difference
it seems rather fragile, and it would mean that NEXT VALUE FOR doesn't
act like a function in some syntactic contexts like a FROM-function.
regards, tom lane