Hi 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).
Here is a starter patch to add it. To avoid a shift/reduce conflict, I had to reclassify the keyword NEXT. I admit that I don't fully understand the consequences of that change! Please let me know if you think this could fly. Best regards, Thomas Munro
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 3a7cfa9..f9ab887 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10778,6 +10778,9 @@ table2-mapping <indexterm> <primary>setval</primary> </indexterm> + <indexterm> + <primary>NEXT VALUE FOR</primary> + </indexterm> <para> This section describes functions for operating on <firstterm>sequence @@ -10817,6 +10820,11 @@ table2-mapping <entry>Advance sequence and return new value</entry> </row> <row> + <entry><literal><function>NEXT VALUE FOR <type>sequence_name</type></function></literal></entry> + <entry><type>bigint</type></entry> + <entry>Advance sequence and return new value, using SQL 2003 syntax</entry> + </row> + <row> <entry><literal><function>setval(<type>regclass</type>, <type>bigint</type>)</function></literal></entry> <entry><type>bigint</type></entry> <entry>Set sequence's current value</entry> @@ -10929,6 +10937,24 @@ nextval('foo'::text) <lineannotation><literal>foo</literal> is looked up at </varlistentry> <varlistentry> + <term><function>NEXT VALUE FOR</function></term> + <listitem> + <para> + The SQL standard specifies this syntax for getting the next value from + a sequence object. It is equivalent to <function>nextval</function>, + but takes a sequence name directly rather than a <type>regclass</type> + or a <type>text</type> value. This form is more portable to other + databases. The following statements are equivalent: + +<screen> +SELECT nextval('foo_id_seq'); +SELECT NEXT VALUE FOR foo_id_seq; +</screen> + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><function>currval</function></term> <listitem> <para> diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 77d2f29..acbe13c 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11936,6 +11936,17 @@ func_expr_common_subexpr: v->location = @1; $$ = (Node *)v; } + | NEXT VALUE_P FOR any_name + { + /* + * Translate as "nextval(<name>::regclass)". + */ + char *name = NameListToString($4); + $$ = (Node *) makeFuncCall(SystemFuncName("nextval"), + list_make1(makeStringConstCast(name, @4, + SystemTypeName("regclass"))), + @1); + } | XMLCONCAT '(' expr_list ')' { $$ = makeXmlExpr(IS_XMLCONCAT, NULL, NIL, $3, @1); @@ -13157,7 +13168,6 @@ unreserved_keyword: | MOVE | NAME_P | NAMES - | NEXT | NO | NOTHING | NOTIFY @@ -13371,6 +13381,7 @@ type_func_name_keyword: | LEFT | LIKE | NATURAL + | NEXT | NOTNULL | OUTER_P | OVERLAPS diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 3c8c1b9..90a3b09 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -245,7 +245,7 @@ PG_KEYWORD("names", NAMES, UNRESERVED_KEYWORD) PG_KEYWORD("national", NATIONAL, COL_NAME_KEYWORD) PG_KEYWORD("natural", NATURAL, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("nchar", NCHAR, COL_NAME_KEYWORD) -PG_KEYWORD("next", NEXT, UNRESERVED_KEYWORD) +PG_KEYWORD("next", NEXT, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("no", NO, UNRESERVED_KEYWORD) PG_KEYWORD("none", NONE, COL_NAME_KEYWORD) PG_KEYWORD("not", NOT, RESERVED_KEYWORD) diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out index a27b5fd..0e3ade0 100644 --- a/src/test/regress/expected/sequence.out +++ b/src/test/regress/expected/sequence.out @@ -168,6 +168,13 @@ SELECT nextval('sequence_test'::text); DISCARD SEQUENCES; SELECT currval('sequence_test'::regclass); ERROR: currval of sequence "sequence_test" is not yet defined in this session +-- SQL:2003 syntax +SELECT NEXT VALUE FOR sequence_test; + nextval +--------- + 100 +(1 row) + DROP SEQUENCE sequence_test; -- renaming sequences CREATE SEQUENCE foo_seq; diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql index 8d3b700..c90641d 100644 --- a/src/test/regress/sql/sequence.sql +++ b/src/test/regress/sql/sequence.sql @@ -76,6 +76,9 @@ SELECT nextval('sequence_test'::text); DISCARD SEQUENCES; SELECT currval('sequence_test'::regclass); +-- SQL:2003 syntax +SELECT NEXT VALUE FOR sequence_test; + DROP SEQUENCE sequence_test; -- renaming sequences
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers