The SQL standard has the expression "NEXT VALUE FOR asequence" to do
what we traditionally do with "nextval('asequence')".
This is an attempt to implement this on top of the recently introduced
NextValueExpr node.
If there is no obvious reason why we would not want that, I'll add it
to the next commitfest.
Is this behavior ok:
test=> CREATE SEQUENCE testseq;
test=> PREPARE x AS SELECT NEXT VALUE FOR testseq;
test=> EXECUTE x;
?column?
----------
1
(1 row)
test=> DROP SEQUENCE testseq;
DROP SEQUENCE
test=> EXECUTE x;
ERROR: could not open relation with OID 24836
If not, what could be done about it?
Yours,
Laurenz AlbeFrom 57e42f0a32be39eea541808979a41ab6feac6b73 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.a...@cybertec.at>
Date: Mon, 19 Feb 2018 12:17:59 +0100
Subject: [PATCH] Add support for NEXT VALUE FOR <sequence>
NEXT VALUE FOR is the standard SQL expression for getting the next
value from a sequence, which in PostgreSQL traditionally has been
the task of the "nextval" function.
This completes the implementation of SQL standard feature T176.
---
doc/src/sgml/func.sgml | 10 ++++++++
doc/src/sgml/ref/create_sequence.sgml | 3 ++-
doc/src/sgml/syntax.sgml | 18 ++++++++++++++
src/backend/commands/tablecmds.c | 2 ++
src/backend/executor/execExpr.c | 1 +
src/backend/executor/execExprInterp.c | 3 ++-
src/backend/nodes/copyfuncs.c | 2 ++
src/backend/nodes/equalfuncs.c | 2 ++
src/backend/nodes/outfuncs.c | 2 ++
src/backend/nodes/readfuncs.c | 2 ++
src/backend/parser/gram.y | 6 +++++
src/backend/parser/parse_expr.c | 43 ++++++++++++++++++++++++++++++++++
src/backend/rewrite/rewriteHandler.c | 2 ++
src/backend/utils/adt/ruleutils.c | 12 +++-------
src/include/executor/execExpr.h | 1 +
src/include/nodes/primnodes.h | 7 ++++--
src/test/regress/expected/sequence.out | 32 ++++++++++++++++++++++++-
src/test/regress/sql/sequence.sql | 12 +++++++++-
18 files changed, 145 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 487c7ff750..e135a05314 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12095,6 +12095,16 @@ nextval('foo'::text) <lineannotation><literal>foo</literal> is looked up at
This function requires <literal>USAGE</literal>
or <literal>UPDATE</literal> privilege on the sequence.
</para>
+
+ <note>
+ <para>
+ PostgreSQL also offers the SQL standard compliant expression
+ <literal>NEXT VALUE FOR <replaceable>sequence_name</replaceable></literal>
+ which is the same as
+ <literal>nextval('<replaceable>sequence_name</replaceable>')</literal>.
+ See <xref linkend="sql-syntax-next-value-for"/> for details.
+ </para>
+ </note>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml
index 3e0d339c85..2671e80f12 100644
--- a/doc/src/sgml/ref/create_sequence.sgml
+++ b/doc/src/sgml/ref/create_sequence.sgml
@@ -51,7 +51,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="param
</para>
<para>
- After a sequence is created, you use the functions
+ After a sequence is created, you use the expression
+ <literal>NEXT VALUE FOR</literal> or the functions
<function>nextval</function>,
<function>currval</function>, and
<function>setval</function>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index a938a21334..85fa136753 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -2379,6 +2379,24 @@ SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows
</sect2>
+ <sect2 id="sql-syntax-next-value-for">
+ <title><literal>NEXT VALUE FOR</literal></title>
+
+ <indexterm>
+ <primary>NEXT VALUE FOR</primary>
+ </indexterm>
+
+ <para>
+ This expression is used to obtain the next value for a sequence.
+<synopsis>
+<literal>NEXT VALUE FOR</literal> <replaceable>sequence_name</replaceable>
+</synopsis>
+
+ It is equivalent to calling the <function>nextval</function> function.
+ </para>
+
+ </sect2>
+
<sect2 id="syntax-express-eval">
<title>Expression Evaluation Rules</title>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 89454d8e80..34a1b0684d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5496,6 +5496,8 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
nve->seqid = RangeVarGetRelid(colDef->identitySequence, NoLock, false);
nve->typeId = typeOid;
+ /* no need to check permissions on the implicit sequence */
+ nve->checkperms = false;
defval = (Expr *) nve;
}
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index c6eb3ebacf..2f6b536d90 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -2104,6 +2104,7 @@ ExecInitExprRec(Expr *node, ExprState *state,
scratch.opcode = EEOP_NEXTVALUEEXPR;
scratch.d.nextvalueexpr.seqid = nve->seqid;
scratch.d.nextvalueexpr.seqtypid = nve->typeId;
+ scratch.d.nextvalueexpr.checkperms = nve->checkperms;
ExprEvalPushStep(state, &scratch);
break;
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index f646fd9c51..1b067eb491 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -2355,7 +2355,8 @@ ExecEvalCurrentOfExpr(ExprState *state, ExprEvalStep *op)
void
ExecEvalNextValueExpr(ExprState *state, ExprEvalStep *op)
{
- int64 newval = nextval_internal(op->d.nextvalueexpr.seqid, false);
+ int64 newval = nextval_internal(op->d.nextvalueexpr.seqid,
+ op->d.nextvalueexpr.checkperms);
switch (op->d.nextvalueexpr.seqtypid)
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index bafe0d1071..aaadc8b54b 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2022,6 +2022,8 @@ _copyNextValueExpr(const NextValueExpr *from)
COPY_SCALAR_FIELD(seqid);
COPY_SCALAR_FIELD(typeId);
+ COPY_NODE_FIELD(relation);
+ COPY_SCALAR_FIELD(checkperms);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 02ca7d588c..31b3f7ca76 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -737,6 +737,8 @@ _equalNextValueExpr(const NextValueExpr *a, const NextValueExpr *b)
{
COMPARE_SCALAR_FIELD(seqid);
COMPARE_SCALAR_FIELD(typeId);
+ COMPARE_NODE_FIELD(relation);
+ COMPARE_SCALAR_FIELD(checkperms);
return true;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index e6ba096257..7eb73c31f9 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1629,6 +1629,8 @@ _outNextValueExpr(StringInfo str, const NextValueExpr *node)
WRITE_OID_FIELD(seqid);
WRITE_OID_FIELD(typeId);
+ WRITE_NODE_FIELD(relation);
+ WRITE_BOOL_FIELD(checkperms);
}
static void
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 22d8b9d0d5..0e88cc7df7 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1218,6 +1218,8 @@ _readNextValueExpr(void)
READ_OID_FIELD(seqid);
READ_OID_FIELD(typeId);
+ READ_NODE_FIELD(relation);
+ READ_BOOL_FIELD(checkperms);
READ_DONE();
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 5329432f25..6db50c294e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -13422,6 +13422,12 @@ c_expr: columnref { $$ = $1; }
{ $$ = $1; }
| func_expr
{ $$ = $1; }
+ | NEXT VALUE_P FOR qualified_name
+ {
+ NextValueExpr *n = makeNode(NextValueExpr);
+ n->relation = $4;
+ $$ = (Node *)n;
+ }
| select_with_parens %prec UMINUS
{
SubLink *n = makeNode(SubLink);
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index b2f5e46e3b..1d5c5d79db 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -15,8 +15,12 @@
#include "postgres.h"
+#include "access/htup_details.h"
+#include "catalog/pg_class.h"
+#include "catalog/pg_sequence.h"
#include "catalog/pg_type.h"
#include "commands/dbcommands.h"
+#include "commands/sequence.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -36,6 +40,7 @@
#include "utils/builtins.h"
#include "utils/date.h"
#include "utils/lsyscache.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
#include "utils/xml.h"
@@ -109,6 +114,7 @@ static Node *transformArrayExpr(ParseState *pstate, A_ArrayExpr *a,
static Node *transformRowExpr(ParseState *pstate, RowExpr *r, bool allowDefault);
static Node *transformCoalesceExpr(ParseState *pstate, CoalesceExpr *c);
static Node *transformMinMaxExpr(ParseState *pstate, MinMaxExpr *m);
+static Node *transformNextValueExpr(ParseState *pstate, NextValueExpr *nv);
static Node *transformSQLValueFunction(ParseState *pstate,
SQLValueFunction *svf);
static Node *transformXmlExpr(ParseState *pstate, XmlExpr *x);
@@ -303,6 +309,10 @@ transformExprRecurse(ParseState *pstate, Node *expr)
result = transformMinMaxExpr(pstate, (MinMaxExpr *) expr);
break;
+ case T_NextValueExpr:
+ result = transformNextValueExpr(pstate, (NextValueExpr *) expr);
+ break;
+
case T_SQLValueFunction:
result = transformSQLValueFunction(pstate,
(SQLValueFunction *) expr);
@@ -2288,6 +2298,39 @@ transformMinMaxExpr(ParseState *pstate, MinMaxExpr *m)
return (Node *) newm;
}
+static Node *
+transformNextValueExpr(ParseState *pstate, NextValueExpr *nv)
+{
+ HeapTuple tuple;
+ Form_pg_sequence pg_seq_tuple;
+
+ nv->seqid = RangeVarGetRelid(nv->relation, AccessShareLock, false);
+ nv->checkperms = true;
+
+ if (get_rel_relkind(nv->seqid) != RELKIND_SEQUENCE)
+ {
+ if (nv->relation->schemaname)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\".\"%s\" is not a sequence", nv->relation->schemaname, nv->relation->relname)));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a sequence", nv->relation->relname)));
+ }
+
+ tuple = SearchSysCache1(SEQRELID, ObjectIdGetDatum(nv->seqid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for sequence %u", nv->seqid);
+
+ pg_seq_tuple = (Form_pg_sequence) GETSTRUCT(tuple);
+ nv->typeId = pg_seq_tuple->seqtypid;
+
+ ReleaseSysCache(tuple);
+
+ return (Node *) nv;
+}
+
static Node *
transformSQLValueFunction(ParseState *pstate, SQLValueFunction *svf)
{
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 66253fc3d3..7b25c20794 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -1119,6 +1119,8 @@ build_column_default(Relation rel, int attrno)
nve->seqid = getOwnedSequence(RelationGetRelid(rel), attrno);
nve->typeId = att_tup->atttypid;
+ /* no need to check permissions on the implicit sequence */
+ nve->checkperms = false;
return (Node *) nve;
}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index c5f5a1ca3f..2e1b2f1b49 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8651,15 +8651,9 @@ get_rule_expr(Node *node, deparse_context *context,
{
NextValueExpr *nvexpr = (NextValueExpr *) node;
- /*
- * This isn't exactly nextval(), but that seems close enough
- * for EXPLAIN's purposes.
- */
- appendStringInfoString(buf, "nextval(");
- simple_quote_literal(buf,
- generate_relation_name(nvexpr->seqid,
- NIL));
- appendStringInfoChar(buf, ')');
+ appendStringInfo(buf, "NEXT VALUE FOR %s",
+ generate_relation_name(nvexpr->seqid,
+ NIL));
}
break;
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index 117fc892f4..04ffae2285 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -394,6 +394,7 @@ typedef struct ExprEvalStep
{
Oid seqid;
Oid seqtypid;
+ bool checkperms;
} nextvalueexpr;
/* for EEOP_ARRAYEXPR */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 1b4b0d75af..cc8a163b02 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1289,8 +1289,8 @@ typedef struct CurrentOfExpr
/*
* NextValueExpr - get next value from sequence
*
- * This has the same effect as calling the nextval() function, but it does not
- * check permissions on the sequence. This is used for identity columns,
+ * This has the same effect as calling the nextval() function.
+ * It is used for NEXT VALUE FOR expressions and identity columns.
* where the sequence is an implicit dependency without its own permissions.
*/
typedef struct NextValueExpr
@@ -1298,6 +1298,9 @@ typedef struct NextValueExpr
Expr xpr;
Oid seqid;
Oid typeId;
+ RangeVar *relation;
+ bool checkperms; /* false for identity columns, since the
+ sequence is an implicit dependency */
} NextValueExpr;
/*
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index ca5ea063fa..345f10fca8 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -237,7 +237,37 @@ 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
-DROP SEQUENCE sequence_test;
+-- get sequence value with NEXT VALUE FOR
+SELECT NEXT VALUE FOR sequence_test;
+ ?column?
+----------
+ 100
+(1 row)
+
+CREATE VIEW seq_v(s) AS SELECT NEXT VALUE FOR sequence_test;
+SELECT s FROM seq_v;
+ s
+-----
+ 101
+(1 row)
+
+SELECT pg_get_viewdef('seq_v'::regclass);
+ pg_get_viewdef
+--------------------------------------------
+ SELECT NEXT VALUE FOR sequence_test AS s;
+(1 row)
+
+BEGIN;
+ALTER SEQUENCE sequence_test RENAME TO "bogus seq";
+SELECT pg_get_viewdef('seq_v'::regclass);
+ pg_get_viewdef
+------------------------------------------
+ SELECT NEXT VALUE FOR "bogus seq" AS s;
+(1 row)
+
+ROLLBACK;
+DROP SEQUENCE sequence_test CASCADE;
+NOTICE: drop cascades to view seq_v
-- 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 a7b9e63372..cdadac5c0b 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -128,7 +128,17 @@ SELECT nextval('sequence_test'::text);
DISCARD SEQUENCES;
SELECT currval('sequence_test'::regclass);
-DROP SEQUENCE sequence_test;
+-- get sequence value with NEXT VALUE FOR
+SELECT NEXT VALUE FOR sequence_test;
+CREATE VIEW seq_v(s) AS SELECT NEXT VALUE FOR sequence_test;
+SELECT s FROM seq_v;
+SELECT pg_get_viewdef('seq_v'::regclass);
+BEGIN;
+ALTER SEQUENCE sequence_test RENAME TO "bogus seq";
+SELECT pg_get_viewdef('seq_v'::regclass);
+ROLLBACK;
+
+DROP SEQUENCE sequence_test CASCADE;
-- renaming sequences
CREATE SEQUENCE foo_seq;
--
2.14.3
