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
