On 2 October 2014 14:48, Tom Lane <[email protected]> wrote:
> Thomas Munro <[email protected]> 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/[email protected]
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/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml
index 7292c3f..ed43f52 100644
--- a/doc/src/sgml/ref/create_sequence.sgml
+++ b/doc/src/sgml/ref/create_sequence.sgml
@@ -50,7 +50,7 @@ CREATE [ TEMPORARY | TEMP ] [ IF NOT EXISTS ] SEQUENCE <replaceable class="param
<para>
After a sequence is created, you use the functions
- <function>nextval</function>,
+ <function>NEXT VALUE FOR</function>, <function>nextval</function>,
<function>currval</function>, and
<function>setval</function>
to operate on the sequence. These functions are documented in
@@ -314,9 +314,21 @@ SELECT nextval('serial');
</para>
<para>
+ Select the next number from this sequence, this time using the SQL 2003
+ syntax:
+<programlisting>
+SELECT NEXT VALUE FOR serial;
+
+ nextval
+---------
+ 103
+</programlisting>
+ </para>
+
+ <para>
Use this sequence in an <command>INSERT</command> command:
<programlisting>
-INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
+INSERT INTO distributors VALUES (NEXT VALUE FOR serial, 'nothing');
</programlisting>
</para>
@@ -345,8 +357,8 @@ END;
</listitem>
<listitem>
<para>
- Obtaining the next value is done using the <function>nextval()</>
- function instead of the standard's <command>NEXT VALUE FOR</command>
+ Obtaining the next value is done using either the <function>nextval()</>
+ function or the standard's <command>NEXT VALUE FOR</command>
expression.
</para>
</listitem>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 77d2f29..cf28633 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 = NameListToQuotedString($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..5a32b9c 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -169,6 +169,24 @@ DISCARD SEQUENCES;
SELECT currval('sequence_test'::regclass);
ERROR: currval of sequence "sequence_test" is not yet defined in this session
DROP SEQUENCE sequence_test;
+-- SQL:2003 syntax -- simple
+CREATE SEQUENCE seq1;
+SELECT NEXT VALUE FOR seq1;
+ nextval
+---------
+ 1
+(1 row)
+
+DROP SEQUENCE seq1;
+-- SQL:2003 syntax with double quoted identifiers and schema qualifier
+CREATE SEQUENCE "public.seQUENCE";
+SELECT NEXT VALUE FOR "public.seQUENCE";
+ nextval
+---------
+ 1
+(1 row)
+
+DROP SEQUENCE "public.seQUENCE";
-- renaming sequences
CREATE SEQUENCE foo_seq;
ALTER TABLE foo_seq RENAME TO foo_seq_new;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 8d3b700..64a4ca1 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -78,6 +78,16 @@ SELECT currval('sequence_test'::regclass);
DROP SEQUENCE sequence_test;
+-- SQL:2003 syntax -- simple
+CREATE SEQUENCE seq1;
+SELECT NEXT VALUE FOR seq1;
+DROP SEQUENCE seq1;
+
+-- SQL:2003 syntax with double quoted identifiers and schema qualifier
+CREATE SEQUENCE "public.seQUENCE";
+SELECT NEXT VALUE FOR "public.seQUENCE";
+DROP SEQUENCE "public.seQUENCE";
+
-- renaming sequences
CREATE SEQUENCE foo_seq;
ALTER TABLE foo_seq RENAME TO foo_seq_new;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
