nocfbot-0003-nav-by-name.txt
text/plain
Filename: nocfbot-0003-nav-by-name.txt
Type: text/plain
Part: 1
Message:
Re: Row pattern recognition
From 7f135d9dee30c6c4c86f470a97996880b0f6ece2 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Mon, 15 Jun 2026 15:29:59 +0900
Subject: [PATCH 03/13] Recognize row pattern navigation operations by name in
DEFINE
PREV, NEXT, FIRST, and LAST were placeholder functions in pg_proc that
polluted the ordinary function namespace and could be silently misbound to
same-named user functions. Recognize them by name inside a DEFINE clause
and drop the placeholders; an unqualified call is always navigation, while
a schema-qualified call still reaches an ordinary function. Also reject a
navigation operation inside a navigation offset, which must be a run-time
constant and previously could crash the planner.
Recognition happens in two steps in ParseFuncOrColumn: note the matched
name up front, skip the catalog lookup by treating it as FUNCDETAIL_NORMAL,
let the common decoration and wrong-kind-of-routine checks run, and only
then route to ParseRPRNavCall to build the RPRNavExpr. ParseRPRNavCall
therefore does not duplicate the aggregate/window decoration checks
(agg_star, DISTINCT, WITHIN GROUP, ORDER BY, FILTER, OVER); the common path
performs them with identical messages.
Document this in func-window.sgml.
---
doc/src/sgml/func/func-window.sgml | 6 +
src/backend/parser/parse_func.c | 296 ++++++++++-----
src/backend/parser/parse_rpr.c | 15 +-
src/backend/utils/adt/ruleutils.c | 55 ++-
src/backend/utils/adt/windowfuncs.c | 118 ------
src/include/catalog/pg_proc.dat | 24 --
src/test/regress/expected/rpr.out | 32 --
src/test/regress/expected/rpr_base.out | 492 ++++++++++++++++++++++++-
src/test/regress/sql/rpr.sql | 15 -
src/test/regress/sql/rpr_base.sql | 251 +++++++++++++
10 files changed, 1007 insertions(+), 297 deletions(-)
diff --git a/doc/src/sgml/func/func-window.sgml b/doc/src/sgml/func/func-window.sgml
index ab469b56fd7..1079b6abb6e 100644
--- a/doc/src/sgml/func/func-window.sgml
+++ b/doc/src/sgml/func/func-window.sgml
@@ -282,6 +282,10 @@ IGNORE NULLS
Row Pattern Recognition navigation functions are listed in
<xref linkend="functions-rpr-navigation-table"/>. These functions
can be used to describe the DEFINE clause of Row Pattern Recognition.
+ The names <function>PREV</function>, <function>NEXT</function>,
+ <function>FIRST</function>, and <function>LAST</function> are
+ recognized as navigation functions only in an unqualified call; a
+ schema-qualified call is resolved as an ordinary function instead.
</para>
<table id="functions-rpr-navigation-table">
@@ -397,6 +401,8 @@ IGNORE NULLS
permitted. Same-category nesting (e.g.,
<function>PREV</function> inside <function>PREV</function>) is also
prohibited.
+ The <parameter>offset</parameter> argument must be a run-time constant:
+ it cannot reference columns or contain a navigation operation.
</para>
<note>
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 1f6c8fa4fb2..f3b37aa992c 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -31,7 +31,6 @@
#include "parser/parse_target.h"
#include "parser/parse_type.h"
#include "utils/builtins.h"
-#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
@@ -49,6 +48,9 @@ static void unify_hypothetical_args(ParseState *pstate,
List *fargs, int numAggregatedArgs,
Oid *actual_arg_types, Oid *declared_arg_types);
static Oid FuncNameAsType(List *funcname);
+static Node *ParseRPRNavCall(ParseState *pstate, List *funcname,
+ List *fargs, List *argnames, FuncCall *fn,
+ int location);
static Node *ParseComplexProjection(ParseState *pstate, const char *funcname,
Node *first_arg, int location);
static Oid LookupFuncNameInternal(ObjectType objtype, List *funcname,
@@ -122,6 +124,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
int fgc_flags;
char aggkind = 0;
ParseCallbackState pcbstate;
+ bool could_be_rpr_nav = false;
/*
* If there's an aggregate filter, transform it using transformWhereClause
@@ -218,6 +221,28 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
Assert(first_arg != NULL);
}
+ /*
+ * Inside an RPR DEFINE clause, an unqualified call to one of the row
+ * pattern navigation names PREV/NEXT/FIRST/LAST denotes the navigation
+ * operation, not an ordinary function. Just note that here; the catalog
+ * lookup is skipped and the RPRNavExpr is built at the end, after the
+ * common decoration checks have run (see the could_be_rpr_nav handling
+ * below). A schema-qualified call is the explicit way to reach an
+ * ordinary function of one of these names.
+ */
+ if (!is_column && !proc_call &&
+ pstate->p_expr_kind == EXPR_KIND_RPR_DEFINE &&
+ list_length(funcname) == 1)
+ {
+ const char *name = strVal(linitial(funcname));
+
+ if (strcmp(name, "prev") == 0 ||
+ strcmp(name, "next") == 0 ||
+ strcmp(name, "first") == 0 ||
+ strcmp(name, "last") == 0)
+ could_be_rpr_nav = true;
+ }
+
/*
* Decide whether it's legitimate to consider the construct to be a column
* projection. For that, there has to be a single argument of complex
@@ -266,17 +291,32 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
* with default arguments.
*/
- setup_parser_errposition_callback(&pcbstate, pstate, location);
+ if (!could_be_rpr_nav)
+ {
+ setup_parser_errposition_callback(&pcbstate, pstate, location);
+
+ fdresult = func_get_detail(funcname, fargs, argnames, nargs,
+ actual_arg_types,
+ !func_variadic, true, proc_call,
+ &fgc_flags,
+ &funcid, &rettype, &retset,
+ &nvargs, &vatype,
+ &declared_arg_types, &argdefaults);
- fdresult = func_get_detail(funcname, fargs, argnames, nargs,
- actual_arg_types,
- !func_variadic, true, proc_call,
- &fgc_flags,
- &funcid, &rettype, &retset,
- &nvargs, &vatype,
- &declared_arg_types, &argdefaults);
+ cancel_parser_errposition_callback(&pcbstate);
+ }
+ else
+ {
+ /*
+ * A recognized navigation name skips catalog lookup entirely. Treat
+ * it as an ordinary function so the common wrong-kind-of-routine and
+ * decoration checks below run with the existing messages, then route
+ * to ParseRPRNavCall to build the RPRNavExpr.
+ */
+ Assert(!proc_call);
- cancel_parser_errposition_callback(&pcbstate);
+ fdresult = FUNCDETAIL_NORMAL;
+ }
/*
* Check for various wrong-kind-of-routine cases.
@@ -653,6 +693,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
parser_errposition(pstate, location)));
}
+ /*
+ * A recognized navigation name has now passed the common decoration and
+ * wrong-kind checks above; build the RPRNavExpr. No fallback to function
+ * resolution ever happens here.
+ */
+ if (could_be_rpr_nav)
+ return ParseRPRNavCall(pstate, funcname, fargs, argnames, fn,
+ location);
+
/*
* If there are default arguments, we have to include their types in
* actual_arg_types for the purpose of checking generic type consistency.
@@ -759,88 +808,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
if (retset)
check_srf_call_placement(pstate, last_srf, location);
- /*
- * RPR navigation functions (PREV/NEXT/FIRST/LAST) are only meaningful
- * inside a WINDOW DEFINE clause.
- *
- * Outside DEFINE, these polymorphic placeholders can shadow column access
- * via functional notation (e.g., last(f) meaning f.last). For the 1-arg
- * form, try column projection first; if that succeeds, use it instead.
- * Otherwise, report a clear parser error.
- */
- if (fdresult == FUNCDETAIL_NORMAL &&
- pstate->p_expr_kind != EXPR_KIND_RPR_DEFINE &&
- (funcid == F_PREV_ANYELEMENT || funcid == F_NEXT_ANYELEMENT ||
- funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_ANYELEMENT_INT8 ||
- funcid == F_FIRST_ANYELEMENT || funcid == F_LAST_ANYELEMENT ||
- funcid == F_FIRST_ANYELEMENT_INT8 || funcid == F_LAST_ANYELEMENT_INT8))
- {
- /* 1-arg form: try column projection before erroring out */
- if (nargs == 1 && !agg_star && !agg_distinct && over == NULL &&
- list_length(funcname) == 1)
- {
- Node *projection;
-
- projection = ParseComplexProjection(pstate,
- strVal(linitial(funcname)),
- linitial(fargs),
- location);
- if (projection)
- return projection;
- }
-
- /* Not a column projection -- report error */
- ereport(ERROR,
- errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("cannot use %s outside a DEFINE clause",
- NameListToString(funcname)),
- parser_errposition(pstate, location));
- }
-
/* build the appropriate output structure */
- if (fdresult == FUNCDETAIL_NORMAL &&
- pstate->p_expr_kind == EXPR_KIND_RPR_DEFINE &&
- (funcid == F_PREV_ANYELEMENT || funcid == F_NEXT_ANYELEMENT ||
- funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_ANYELEMENT_INT8 ||
- funcid == F_FIRST_ANYELEMENT || funcid == F_LAST_ANYELEMENT ||
- funcid == F_FIRST_ANYELEMENT_INT8 || funcid == F_LAST_ANYELEMENT_INT8))
- {
- /*
- * RPR navigation functions (PREV/NEXT/FIRST/LAST) are compiled into
- * EEOP_RPR_NAV_SET / EEOP_RPR_NAV_RESTORE opcodes instead of a normal
- * function call. Represent them as RPRNavExpr nodes so that later
- * stages can identify them without relying on funcid comparisons.
- */
- RPRNavKind kind;
- bool has_offset;
- RPRNavExpr *navexpr;
-
- if (funcid == F_PREV_ANYELEMENT || funcid == F_PREV_ANYELEMENT_INT8)
- kind = RPR_NAV_PREV;
- else if (funcid == F_NEXT_ANYELEMENT || funcid == F_NEXT_ANYELEMENT_INT8)
- kind = RPR_NAV_NEXT;
- else if (funcid == F_FIRST_ANYELEMENT || funcid == F_FIRST_ANYELEMENT_INT8)
- kind = RPR_NAV_FIRST;
- else
- kind = RPR_NAV_LAST;
-
- has_offset = (funcid == F_PREV_ANYELEMENT_INT8 ||
- funcid == F_NEXT_ANYELEMENT_INT8 ||
- funcid == F_FIRST_ANYELEMENT_INT8 ||
- funcid == F_LAST_ANYELEMENT_INT8);
-
- navexpr = makeNode(RPRNavExpr);
-
- navexpr->kind = kind;
- navexpr->arg = (Expr *) linitial(fargs);
- navexpr->offset_arg = has_offset ? (Expr *) lsecond(fargs) : NULL;
- navexpr->resulttype = rettype;
- /* resultcollid will be set by parse_collate.c */
- navexpr->location = location;
-
- retval = (Node *) navexpr;
- }
- else if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE)
+ if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE)
{
FuncExpr *funcexpr = makeNode(FuncExpr);
@@ -2111,6 +2080,151 @@ FuncNameAsType(List *funcname)
return result;
}
+/*
+ * ParseRPRNavCall
+ * Recognize a row pattern navigation operation in a DEFINE clause.
+ *
+ * Inside an EXPR_KIND_RPR_DEFINE clause an unqualified call to one of the
+ * names PREV/NEXT/FIRST/LAST denotes the corresponding row pattern navigation
+ * operation (ISO/IEC 19075-5 Subclause 5.6), not an ordinary function call.
+ * The name is matched here, before any catalog lookup, with no fallback to
+ * function resolution: once it matches, decoration and argument-count
+ * violations are dedicated errors rather than letting an ordinary function of
+ * the same name take over. A schema-qualified call (the caller restricts us
+ * to unqualified names) is the documented way to reach such a function
+ * instead.
+ *
+ * The caller routes here only after the name has matched one of the four
+ * navigation names and the common decoration/wrong-kind checks in
+ * ParseFuncOrColumn have run, so this always returns an RPRNavExpr.
+ */
+static Node *
+ParseRPRNavCall(ParseState *pstate, List *funcname, List *fargs,
+ List *argnames, FuncCall *fn, int location)
+{
+ const char *name = strVal(linitial(funcname));
+ RPRNavKind kind;
+ const char *navname;
+ int nargs = list_length(fargs);
+ Node *arg;
+ RPRNavExpr *navexpr;
+
+ /* match the parser-downcased identifier; otherwise not a navigation name */
+ if (strcmp(name, "prev") == 0)
+ {
+ kind = RPR_NAV_PREV;
+ navname = "PREV";
+ }
+ else if (strcmp(name, "next") == 0)
+ {
+ kind = RPR_NAV_NEXT;
+ navname = "NEXT";
+ }
+ else if (strcmp(name, "first") == 0)
+ {
+ kind = RPR_NAV_FIRST;
+ navname = "FIRST";
+ }
+ else if (strcmp(name, "last") == 0)
+ {
+ kind = RPR_NAV_LAST;
+ navname = "LAST";
+ }
+ else
+ {
+ /* the caller only routes here after matching one of the four names */
+ pg_unreachable();
+ return NULL;
+ }
+
+ /*
+ * Once the name matches we never fall back to function resolution, so any
+ * decoration that does not make sense for a navigation operation is a
+ * hard error. The aggregate/window decorations (agg_star, DISTINCT,
+ * WITHIN GROUP, ORDER BY, FILTER, OVER, RESPECT/IGNORE NULLS) are already
+ * rejected by the common path in ParseFuncOrColumn, which treated the
+ * recognized name as an ordinary function; what remains are the
+ * decorations that path accepts for a plain function but a navigation
+ * operation must still reject.
+ */
+ if (fn->func_variadic)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot use VARIADIC with row pattern navigation function %s",
+ navname),
+ parser_errposition(pstate, location)));
+ if (argnames != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("row pattern navigation operations cannot use named arguments"),
+ parser_errposition(pstate, location)));
+ /* takes a value expression and an optional offset */
+ if (nargs == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("too few arguments for row pattern navigation function %s",
+ navname),
+ errdetail("%s takes a value expression and an optional offset argument.",
+ navname),
+ parser_errposition(pstate, location)));
+ if (nargs > 2)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("too many arguments for row pattern navigation function %s",
+ navname),
+ errdetail("%s takes a value expression and an optional offset argument.",
+ navname),
+ parser_errposition(pstate, location)));
+
+ /*
+ * Resolve a still-unknown first argument to text, the same way the
+ * anycompatible family does. A navigation operation is not a polymorphic
+ * function, so the old "could not determine polymorphic type" error does
+ * not apply; an unknown literal cannot contain a column reference, so the
+ * walker still rejects it later.
+ */
+ arg = linitial(fargs);
+ if (exprType(arg) == UNKNOWNOID)
+ arg = coerce_to_common_type(pstate, arg, TEXTOID, navname);
+
+ navexpr = makeNode(RPRNavExpr);
+ navexpr->kind = kind;
+ navexpr->arg = (Expr *) arg;
+
+ /* an explicit offset is coerced to int8, which the executor reads */
+ if (nargs == 2)
+ {
+ Node *offset = lsecond(fargs);
+ Oid offtype = exprType(offset);
+
+ if (offtype != INT8OID)
+ {
+ Node *newoffset;
+
+ newoffset = coerce_to_target_type(pstate, offset, offtype,
+ INT8OID, -1, COERCION_IMPLICIT,
+ COERCE_IMPLICIT_CAST, -1);
+ if (newoffset == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("offset argument of %s must be type %s, not type %s",
+ navname, "bigint", format_type_be(offtype)),
+ parser_errposition(pstate, exprLocation(offset))));
+ offset = newoffset;
+ }
+ navexpr->offset_arg = (Expr *) offset;
+ }
+ else
+ navexpr->offset_arg = NULL;
+
+ /* compound_offset_arg stays NULL; define_walker flattening fills it in */
+ navexpr->resulttype = exprType(arg);
+ /* resultcollid will be set by parse_collate.c */
+ navexpr->location = location;
+
+ return (Node *) navexpr;
+}
+
/*
* ParseComplexProjection -
* handles function calls with a single argument that is of complex type.
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index 3eaea2be750..8ed01bb8f28 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -472,6 +472,7 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
* * PREV/NEXT wrapping FIRST/LAST flattens to a compound kind
* * Other nestings are rejected (FIRST(PREV()), PREV(PREV()), ...)
* * offset_arg / compound_offset_arg must not contain column refs
+ * or nested navigation operations
*
* Volatile callees (and sequence operations) are rejected later in the
* planner via validate_rpr_define_volatility(); see optimizer/plan/rpr.c.
@@ -482,7 +483,7 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
* walks nav.arg in PHASE_NAV_ARG to collect nesting/column-ref state,
* applies compound flatten or raises a nesting error, then walks the
* (post-flatten) offset(s) in PHASE_NAV_OFFSET to enforce the
- * constant-offset rule. No subtree is walked twice.
+ * constant-offset and no-nested-nav rules. No subtree is walked twice.
*/
/*
@@ -498,6 +499,7 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
* PREV(PREV()), FIRST(FIRST()), three-or-more deep)
* [2] for each nav offset (PHASE_NAV_OFFSET):
* - must be a run-time constant (no column references)
+ * - must not contain a row pattern navigation operation
*
* Var sightings feed the column-ref rule for the enclosing nav scope;
* RPRNavExpr sightings inside PHASE_NAV_ARG feed the nesting decision.
@@ -538,11 +540,14 @@ define_walker(Node *node, void *context)
if (ctx->phase == DEFINE_PHASE_NAV_OFFSET)
{
/*
- * Navs inside offset_arg are unusual but not directly banned; the
- * constant-offset rule will catch any Var or volatile they
- * contain.
+ * A navigation offset must be a run-time constant, so it cannot
+ * contain a navigation operation.
*/
- return expression_tree_walker(node, define_walker, ctx);
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("row pattern navigation offset cannot contain a row pattern navigation operation"),
+ errdetail("A navigation offset must be a run-time constant."),
+ parser_errposition(ctx->pstate, nav->location));
}
/*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 4eb7e35bee4..2b7fd7367f3 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -127,6 +127,7 @@ typedef struct
bool varprefix; /* true to print prefixes on Vars */
bool colNamesVisible; /* do we care about output column names? */
bool inGroupBy; /* deparsing GROUP BY clause? */
+ bool inRPRDefine; /* deparsing an RPR DEFINE clause? */
bool varInOrderBy; /* deparsing simple Var in ORDER BY? */
Bitmapset *appendparents; /* if not null, map child Vars of these relids
* back to the parent rel */
@@ -545,7 +546,7 @@ static char *generate_qualified_relation_name(Oid relid);
static char *generate_function_name(Oid funcid, int nargs,
List *argnames, Oid *argtypes,
bool has_variadic, bool *use_variadic_p,
- bool inGroupBy);
+ bool inGroupBy, bool inRPRDefine);
static char *generate_operator_name(Oid operid, Oid arg1, Oid arg2);
static void add_cast_to(StringInfo buf, Oid typid);
static char *generate_qualified_type_name(Oid typid);
@@ -1131,6 +1132,7 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
context.indentLevel = PRETTYINDENT_STD;
context.colNamesVisible = true;
context.inGroupBy = false;
+ context.inRPRDefine = false;
context.varInOrderBy = false;
context.appendparents = NULL;
@@ -1142,7 +1144,7 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
appendStringInfo(&buf, "EXECUTE FUNCTION %s(",
generate_function_name(trigrec->tgfoid, 0,
NIL, NULL,
- false, NULL, false));
+ false, NULL, false, false));
if (trigrec->tgnargs > 0)
{
@@ -3401,7 +3403,7 @@ pg_get_functiondef(PG_FUNCTION_ARGS)
appendStringInfo(&buf, " SUPPORT %s",
generate_function_name(proc->prosupport, 1,
NIL, argtypes,
- false, NULL, false));
+ false, NULL, false, false));
}
if (oldlen != buf.len)
@@ -4054,6 +4056,7 @@ deparse_expression_pretty(Node *expr, List *dpcontext,
context.indentLevel = startIndent;
context.colNamesVisible = true;
context.inGroupBy = false;
+ context.inRPRDefine = false;
context.varInOrderBy = false;
context.appendparents = NULL;
@@ -5849,6 +5852,7 @@ make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
context.indentLevel = PRETTYINDENT_STD;
context.colNamesVisible = true;
context.inGroupBy = false;
+ context.inRPRDefine = false;
context.varInOrderBy = false;
context.appendparents = NULL;
@@ -6041,6 +6045,7 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace,
context.indentLevel = startIndent;
context.colNamesVisible = colNamesVisible;
context.inGroupBy = false;
+ context.inRPRDefine = false;
context.varInOrderBy = false;
context.appendparents = NULL;
@@ -7220,15 +7225,25 @@ get_rule_define(List *defineClause, deparse_context *context)
{
StringInfo buf = context->buf;
const char *sep;
+ bool save_inrprdefine = context->inRPRDefine;
sep = " ";
+ /*
+ * Within the DEFINE clause an unqualified prev/next/first/last is a
+ * navigation operation, so a user function of one of those names must be
+ * schema-qualified to survive a reparse; see generate_function_name().
+ */
+ context->inRPRDefine = true;
+
foreach_node(TargetEntry, te, defineClause)
{
appendStringInfo(buf, "%s%s AS ", sep, quote_identifier(te->resname));
get_rule_expr((Node *) te->expr, context, false);
sep = ",\n ";
}
+
+ context->inRPRDefine = save_inrprdefine;
}
/*
@@ -7459,6 +7474,7 @@ get_window_frame_options_for_explain(int frameOptions,
context.indentLevel = 0;
context.colNamesVisible = true;
context.inGroupBy = false;
+ context.inRPRDefine = false;
context.varInOrderBy = false;
context.appendparents = NULL;
@@ -11691,7 +11707,8 @@ get_func_expr(FuncExpr *expr, deparse_context *context,
argnames, argtypes,
expr->funcvariadic,
&use_variadic,
- context->inGroupBy));
+ context->inGroupBy,
+ context->inRPRDefine));
nargs = 0;
foreach(l, expr->args)
{
@@ -11761,7 +11778,8 @@ get_agg_expr_helper(Aggref *aggref, deparse_context *context,
funcname = generate_function_name(aggref->aggfnoid, nargs, NIL,
argtypes, aggref->aggvariadic,
&use_variadic,
- context->inGroupBy);
+ context->inGroupBy,
+ context->inRPRDefine);
/* Print the aggregate name, schema-qualified if needed */
appendStringInfo(buf, "%s(%s", funcname,
@@ -11902,7 +11920,8 @@ get_windowfunc_expr_helper(WindowFunc *wfunc, deparse_context *context,
if (!funcname)
funcname = generate_function_name(wfunc->winfnoid, nargs, argnames,
argtypes, false, NULL,
- context->inGroupBy);
+ context->inGroupBy,
+ context->inRPRDefine);
appendStringInfo(buf, "%s(", funcname);
@@ -13743,7 +13762,7 @@ get_tablesample_def(TableSampleClause *tablesample, deparse_context *context)
appendStringInfo(buf, " TABLESAMPLE %s (",
generate_function_name(tablesample->tsmhandler, 1,
NIL, argtypes,
- false, NULL, false));
+ false, NULL, false, false));
nargs = 0;
foreach(l, tablesample->args)
@@ -14157,12 +14176,14 @@ generate_qualified_relation_name(Oid relid)
*
* inGroupBy must be true if we're deparsing a GROUP BY clause.
*
+ * inRPRDefine must be true if we're deparsing an RPR DEFINE clause.
+ *
* The result includes all necessary quoting and schema-prefixing.
*/
static char *
generate_function_name(Oid funcid, int nargs, List *argnames, Oid *argtypes,
bool has_variadic, bool *use_variadic_p,
- bool inGroupBy)
+ bool inGroupBy, bool inRPRDefine)
{
char *result;
HeapTuple proctup;
@@ -14196,6 +14217,24 @@ generate_function_name(Oid funcid, int nargs, List *argnames, Oid *argtypes,
force_qualify = true;
}
+ /*
+ * Inside a row pattern DEFINE clause, the parser binds an unqualified
+ * prev/next/first/last to a navigation operation before any catalog
+ * lookup, so an unqualified call to a user function of one of those names
+ * would change meaning across a deparse/reparse cycle. Force schema
+ * qualification; the qualified form is the documented escape hatch. Only
+ * the exact lower-case names are at risk: a mixed-case proname deparses
+ * quoted and cannot match the parser's downcased comparison.
+ */
+ if (inRPRDefine)
+ {
+ if (strcmp(proname, "prev") == 0 ||
+ strcmp(proname, "next") == 0 ||
+ strcmp(proname, "first") == 0 ||
+ strcmp(proname, "last") == 0)
+ force_qualify = true;
+ }
+
/*
* Determine whether VARIADIC should be printed. We must do this first
* since it affects the lookup rules in func_get_detail().
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index d15aa0c75db..78b7f05aba2 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -724,121 +724,3 @@ window_nth_value(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
-
-/*
- * prev
- * Catalog placeholder for RPR's PREV navigation operator.
- *
- * The parser transforms prev() calls inside DEFINE into RPRNavExpr nodes,
- * so this function is never reached during normal RPR execution. It exists
- * only so that the parser can resolve the function name from pg_proc.
- * Calls outside DEFINE are rejected by parse_func.c (EXPR_KIND_RPR_DEFINE
- * check). The error below is a defensive measure in case that check is
- * bypassed (e.g., direct C-level function invocation).
- */
-Datum
-window_prev(PG_FUNCTION_ARGS)
-{
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot use PREV() outside a DEFINE clause"));
- PG_RETURN_NULL(); /* not reached */
-}
-
-/*
- * next
- * Catalog placeholder for RPR's NEXT navigation operator.
- * See window_prev() for details.
- */
-Datum
-window_next(PG_FUNCTION_ARGS)
-{
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot use NEXT() outside a DEFINE clause"));
- PG_RETURN_NULL(); /* not reached */
-}
-
-/*
- * prev(value, offset)
- * Catalog placeholder for RPR's PREV navigation operator with offset.
- * See window_prev() for details.
- */
-Datum
-window_prev_offset(PG_FUNCTION_ARGS)
-{
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot use PREV() outside a DEFINE clause"));
- PG_RETURN_NULL(); /* not reached */
-}
-
-/*
- * next(value, offset)
- * Catalog placeholder for RPR's NEXT navigation operator with offset.
- * See window_prev() for details.
- */
-Datum
-window_next_offset(PG_FUNCTION_ARGS)
-{
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot use NEXT() outside a DEFINE clause"));
- PG_RETURN_NULL(); /* not reached */
-}
-
-/*
- * first
- * Catalog placeholder for RPR's FIRST navigation operator.
- * See window_prev() for details.
- */
-Datum
-window_first(PG_FUNCTION_ARGS)
-{
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot use FIRST() outside a DEFINE clause"));
- PG_RETURN_NULL(); /* not reached */
-}
-
-/*
- * last
- * Catalog placeholder for RPR's LAST navigation operator.
- * See window_prev() for details.
- */
-Datum
-window_last(PG_FUNCTION_ARGS)
-{
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot use LAST() outside a DEFINE clause"));
- PG_RETURN_NULL(); /* not reached */
-}
-
-/*
- * first(value, offset)
- * Catalog placeholder for RPR's FIRST navigation operator with offset.
- * See window_prev() for details.
- */
-Datum
-window_first_offset(PG_FUNCTION_ARGS)
-{
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot use FIRST() outside a DEFINE clause"));
- PG_RETURN_NULL(); /* not reached */
-}
-
-/*
- * last(value, offset)
- * Catalog placeholder for RPR's LAST navigation operator with offset.
- * See window_prev() for details.
- */
-Datum
-window_last_offset(PG_FUNCTION_ARGS)
-{
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot use LAST() outside a DEFINE clause"));
- PG_RETURN_NULL(); /* not reached */
-}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b3aa42fc66e..be157a5fbe9 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10967,30 +10967,6 @@
{ oid => '3114', descr => 'fetch the Nth row value',
proname => 'nth_value', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4', prosrc => 'window_nth_value' },
-{ oid => '8126', descr => 'fetch the preceding row value',
- proname => 'prev', provolatile => 's', prorettype => 'anyelement',
- proargtypes => 'anyelement', prosrc => 'window_prev' },
-{ oid => '8128', descr => 'fetch the Nth preceding row value',
- proname => 'prev', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement',
- proargtypes => 'anyelement int8', prosrc => 'window_prev_offset' },
-{ oid => '8127', descr => 'fetch the following row value',
- proname => 'next', provolatile => 's', prorettype => 'anyelement',
- proargtypes => 'anyelement', prosrc => 'window_next' },
-{ oid => '8129', descr => 'fetch the Nth following row value',
- proname => 'next', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement',
- proargtypes => 'anyelement int8', prosrc => 'window_next_offset' },
-{ oid => '8130', descr => 'fetch the first row value within match',
- proname => 'first', provolatile => 's', prorettype => 'anyelement',
- proargtypes => 'anyelement', prosrc => 'window_first' },
-{ oid => '8132', descr => 'fetch the Nth row value within match',
- proname => 'first', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement',
- proargtypes => 'anyelement int8', prosrc => 'window_first_offset' },
-{ oid => '8131', descr => 'fetch the last row value within match',
- proname => 'last', provolatile => 's', prorettype => 'anyelement',
- proargtypes => 'anyelement', prosrc => 'window_last' },
-{ oid => '8133', descr => 'fetch the Nth-from-last row value within match',
- proname => 'last', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement',
- proargtypes => 'anyelement int8', prosrc => 'window_last_offset' },
# functions for range types
{ oid => '3832', descr => 'I/O',
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
index c02c9d75a9a..dc5140fecc9 100644
--- a/src/test/regress/expected/rpr.out
+++ b/src/test/regress/expected/rpr.out
@@ -1021,16 +1021,6 @@ WINDOW w AS (
--
-- Error cases: PREV/NEXT usage restrictions
--
--- PREV outside DEFINE clause
-SELECT prev(price) FROM stock;
-ERROR: cannot use prev outside a DEFINE clause
-LINE 1: SELECT prev(price) FROM stock;
- ^
--- NEXT outside DEFINE clause
-SELECT next(price) FROM stock;
-ERROR: cannot use next outside a DEFINE clause
-LINE 1: SELECT next(price) FROM stock;
- ^
-- Nested PREV
SELECT price FROM stock
WINDOW w AS (
@@ -1598,15 +1588,6 @@ WINDOW w AS (
company2 | 07-10-2023 | 1300 | | | 0
(20 rows)
--- 2-arg PREV/NEXT outside DEFINE clause
-SELECT prev(price, 2) FROM stock;
-ERROR: cannot use prev outside a DEFINE clause
-LINE 1: SELECT prev(price, 2) FROM stock;
- ^
-SELECT next(price, 2) FROM stock;
-ERROR: cannot use next outside a DEFINE clause
-LINE 1: SELECT next(price, 2) FROM stock;
- ^
-- 2-arg PREV/NEXT: negative offset
SELECT company, tdate, price, first_value(price) OVER w
FROM stock
@@ -2134,19 +2115,6 @@ SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS (
DEFINE A AS LAST(val, -1) IS NULL
);
ERROR: row pattern navigation offset must not be negative
--- FIRST/LAST outside DEFINE clause (error cases)
-SELECT first(val) FROM rpr_nav;
-ERROR: cannot use first outside a DEFINE clause
-LINE 1: SELECT first(val) FROM rpr_nav;
- ^
-SELECT last(val) FROM rpr_nav;
-ERROR: cannot use last outside a DEFINE clause
-LINE 1: SELECT last(val) FROM rpr_nav;
- ^
-SELECT first(val, 1) FROM rpr_nav;
-ERROR: cannot use first outside a DEFINE clause
-LINE 1: SELECT first(val, 1) FROM rpr_nav;
- ^
-- Functional notation: should access column, not RPR navigation
CREATE TEMP TABLE rpr_names (prev int, next int, first text, last text);
INSERT INTO rpr_names VALUES (1, 2, 'Joe', 'Blow');
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index 41541898f5a..cf158e1c043 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -1709,9 +1709,10 @@ WINDOW w AS (
B AS val > PREV(val)
)
ORDER BY id;
-ERROR: cannot use prev outside a DEFINE clause
+ERROR: function prev(integer) does not exist
LINE 1: SELECT PREV(id), id, val, COUNT(*) OVER w as cnt
^
+DETAIL: There is no function of that name.
-- NEXT function cannot be used other than in DEFINE
SELECT NEXT(id), id, val, COUNT(*) OVER w as cnt
FROM rpr_nav
@@ -1724,9 +1725,10 @@ WINDOW w AS (
B AS val > PREV(val)
)
ORDER BY id;
-ERROR: cannot use next outside a DEFINE clause
+ERROR: function next(integer) does not exist
LINE 1: SELECT NEXT(id), id, val, COUNT(*) OVER w as cnt
^
+DETAIL: There is no function of that name.
-- FIRST function - reference match_start row
SELECT id, val, COUNT(*) OVER w as cnt
FROM rpr_nav
@@ -1792,15 +1794,376 @@ ORDER BY id;
-- FIRST function cannot be used other than in DEFINE
SELECT FIRST(id), id, val FROM rpr_nav;
-ERROR: cannot use first outside a DEFINE clause
+ERROR: function first(integer) does not exist
LINE 1: SELECT FIRST(id), id, val FROM rpr_nav;
^
+DETAIL: There is no function of that name.
-- LAST function cannot be used other than in DEFINE
SELECT LAST(id), id, val FROM rpr_nav;
-ERROR: cannot use last outside a DEFINE clause
+ERROR: function last(integer) does not exist
LINE 1: SELECT LAST(id), id, val FROM rpr_nav;
^
+DETAIL: There is no function of that name.
DROP TABLE rpr_nav;
+-- Name-space: prev/next/first/last are navigation functions, not ordinary functions
+CREATE SCHEMA rpr_navns;
+SET search_path TO rpr_navns, public;
+CREATE TABLE nt (g text, id int, val int);
+INSERT INTO nt VALUES ('x', 1, 100), ('x', 2, 200), ('x', 3, 150),
+ ('x', 4, 140), ('x', 5, 150);
+-- Outside DEFINE these are ordinary identifiers and resolve to nothing
+SELECT prev(val) FROM nt;
+ERROR: function prev(integer) does not exist
+LINE 1: SELECT prev(val) FROM nt;
+ ^
+DETAIL: There is no function of that name.
+SELECT next(val) FROM nt;
+ERROR: function next(integer) does not exist
+LINE 1: SELECT next(val) FROM nt;
+ ^
+DETAIL: There is no function of that name.
+SELECT prev(val, 2) FROM nt;
+ERROR: function prev(integer, integer) does not exist
+LINE 1: SELECT prev(val, 2) FROM nt;
+ ^
+DETAIL: There is no function of that name.
+SELECT next(val, 2) FROM nt;
+ERROR: function next(integer, integer) does not exist
+LINE 1: SELECT next(val, 2) FROM nt;
+ ^
+DETAIL: There is no function of that name.
+SELECT first(val) FROM nt;
+ERROR: function first(integer) does not exist
+LINE 1: SELECT first(val) FROM nt;
+ ^
+DETAIL: There is no function of that name.
+SELECT last(val) FROM nt;
+ERROR: function last(integer) does not exist
+LINE 1: SELECT last(val) FROM nt;
+ ^
+DETAIL: There is no function of that name.
+SELECT first(val, 1) FROM nt;
+ERROR: function first(integer, integer) does not exist
+LINE 1: SELECT first(val, 1) FROM nt;
+ ^
+DETAIL: There is no function of that name.
+-- A schema-qualified call is also a plain (failing) function lookup
+SELECT pg_catalog.prev(val) FROM nt;
+ERROR: function pg_catalog.prev(integer) does not exist
+LINE 1: SELECT pg_catalog.prev(val) FROM nt;
+ ^
+-- Outside DEFINE, a user-defined function of that name is callable
+CREATE FUNCTION next(numeric) RETURNS numeric AS 'SELECT -999::numeric'
+ LANGUAGE sql IMMUTABLE;
+SELECT next(10);
+ next
+------
+ -999
+(1 row)
+
+-- Inside DEFINE, unqualified PREV is nav whether or not a user prev() exists
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (START UP+)
+ DEFINE START AS TRUE, UP AS val > PREV(val))
+ ORDER BY id;
+ id | val | cnt | last_id
+----+-----+-----+---------
+ 1 | 100 | 2 | 2
+ 2 | 200 | 0 |
+ 3 | 150 | 0 |
+ 4 | 140 | 2 | 5
+ 5 | 150 | 0 |
+(5 rows)
+
+-- A qualified call invokes the function, so its volatility still matters
+-- VOLATILE: unqualified is nav; qualified is rejected as a volatile function
+CREATE FUNCTION prev(integer) RETURNS integer AS 'SELECT -999'
+ LANGUAGE sql VOLATILE;
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (START UP+)
+ DEFINE START AS TRUE, UP AS val > PREV(val))
+ ORDER BY id;
+ id | val | cnt | last_id
+----+-----+-----+---------
+ 1 | 100 | 2 | 2
+ 2 | 200 | 0 |
+ 3 | 150 | 0 |
+ 4 | 140 | 2 | 5
+ 5 | 150 | 0 |
+(5 rows)
+
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+)
+ DEFINE A AS rpr_navns.prev(val) = -999)
+ ORDER BY id;
+ERROR: volatile functions are not allowed in DEFINE clause
+LINE 6: DEFINE A AS rpr_navns.prev(val) = -999)
+ ^
+DROP FUNCTION prev(integer);
+-- IMMUTABLE: unqualified is nav; qualified is the escape hatch and succeeds
+CREATE FUNCTION prev(integer) RETURNS integer AS 'SELECT -999'
+ LANGUAGE sql IMMUTABLE;
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (START UP+)
+ DEFINE START AS TRUE, UP AS val > PREV(val))
+ ORDER BY id;
+ id | val | cnt | last_id
+----+-----+-----+---------
+ 1 | 100 | 2 | 2
+ 2 | 200 | 0 |
+ 3 | 150 | 0 |
+ 4 | 140 | 2 | 5
+ 5 | 150 | 0 |
+(5 rows)
+
+-- (val).prev is attribute notation, so it calls the ordinary function prev(val)
+-- (the IMMUTABLE user prev here), the same as the schema-qualified call below
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+)
+ DEFINE A AS (val).prev = -999)
+ ORDER BY id;
+ id | val | cnt | last_id
+----+-----+-----+---------
+ 1 | 100 | 5 | 5
+ 2 | 200 | 0 |
+ 3 | 150 | 0 |
+ 4 | 140 | 0 |
+ 5 | 150 | 0 |
+(5 rows)
+
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+)
+ DEFINE A AS rpr_navns.prev(val) = -999)
+ ORDER BY id;
+ id | val | cnt | last_id
+----+-----+-----+---------
+ 1 | 100 | 5 | 5
+ 2 | 200 | 0 |
+ 3 | 150 | 0 |
+ 4 | 140 | 0 |
+ 5 | 150 | 0 |
+(5 rows)
+
+-- Zero or more than two arguments is an error, with no function fallback
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV() IS NULL);
+ERROR: too few arguments for row pattern navigation function PREV
+LINE 4: PATTERN (A+) DEFINE A AS PREV() IS NULL);
+ ^
+DETAIL: PREV takes a value expression and an optional offset argument.
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL);
+ERROR: too many arguments for row pattern navigation function PREV
+LINE 4: PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL);
+ ^
+DETAIL: PREV takes a value expression and an optional offset argument.
+-- the error stands even when a user function of that exact arity exists
+CREATE FUNCTION prev(integer, integer, integer) RETURNS integer
+ AS 'SELECT -999' LANGUAGE sql IMMUTABLE;
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL);
+ERROR: too many arguments for row pattern navigation function PREV
+LINE 4: PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL);
+ ^
+DETAIL: PREV takes a value expression and an optional offset argument.
+DROP FUNCTION prev(integer, integer, integer);
+-- Syntactic decoration is rejected
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(*) IS NULL);
+ERROR: prev(*) specified, but prev is not an aggregate function
+LINE 4: PATTERN (A+) DEFINE A AS PREV(*) IS NULL);
+ ^
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(DISTINCT val) IS NULL);
+ERROR: DISTINCT specified, but prev is not an aggregate function
+LINE 4: PATTERN (A+) DEFINE A AS PREV(DISTINCT val) IS NULL);
+ ^
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val ORDER BY val) IS NULL);
+ERROR: ORDER BY specified, but prev is not an aggregate function
+LINE 4: PATTERN (A+) DEFINE A AS PREV(val ORDER BY val) IS NULL)...
+ ^
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val) FILTER (WHERE true) IS NULL);
+ERROR: FILTER specified, but prev is not an aggregate function
+LINE 4: PATTERN (A+) DEFINE A AS PREV(val) FILTER (WHERE true) I...
+ ^
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val) WITHIN GROUP (ORDER BY val) IS NULL);
+ERROR: WITHIN GROUP specified, but prev is not an aggregate function
+LINE 4: PATTERN (A+) DEFINE A AS PREV(val) WITHIN GROUP (ORDER B...
+ ^
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val) OVER () IS NULL);
+ERROR: OVER specified, but prev is not a window function nor an aggregate function
+LINE 4: PATTERN (A+) DEFINE A AS PREV(val) OVER () IS NULL);
+ ^
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(VARIADIC ARRAY[val]) IS NULL);
+ERROR: cannot use VARIADIC with row pattern navigation function PREV
+LINE 4: PATTERN (A+) DEFINE A AS PREV(VARIADIC ARRAY[val]) IS NU...
+ ^
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS prev(x => val) IS NULL);
+ERROR: row pattern navigation operations cannot use named arguments
+LINE 4: PATTERN (A+) DEFINE A AS prev(x => val) IS NULL);
+ ^
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val) IGNORE NULLS IS NULL);
+ERROR: only window functions accept RESPECT/IGNORE NULLS
+LINE 4: PATTERN (A+) DEFINE A AS PREV(val) IGNORE NULLS IS NULL)...
+ ^
+-- Quoting does not escape: "prev" is nav, "PREV" is an ordinary name
+SELECT id, val, count(*) OVER w AS cnt
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (START UP+)
+ DEFINE START AS TRUE, UP AS val > "prev"(val))
+ ORDER BY id;
+ id | val | cnt
+----+-----+-----
+ 1 | 100 | 2
+ 2 | 200 | 0
+ 3 | 150 | 0
+ 4 | 140 | 2
+ 5 | 150 | 0
+(5 rows)
+
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS "PREV"(val) IS NULL);
+ERROR: function PREV(integer) does not exist
+LINE 4: PATTERN (A+) DEFINE A AS "PREV"(val) IS NULL);
+ ^
+DETAIL: There is no function of that name.
+-- A view round-trips: bare PREV stays a navigation function, and a qualified
+-- user prev() stays schema-qualified so it does not reparse as navigation
+CREATE VIEW navns_nav AS
+ SELECT id, count(*) OVER w AS cnt FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (START UP+) DEFINE START AS TRUE, UP AS val > PREV(val));
+CREATE VIEW navns_fn AS
+ SELECT id, count(*) OVER w AS cnt FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS rpr_navns.prev(val) = -999);
+SELECT pg_get_viewdef('navns_nav');
+ pg_get_viewdef
+---------------------------------------------------------------------------------------------
+ SELECT id, +
+ count(*) OVER w AS cnt +
+ FROM nt +
+ WINDOW w AS (PARTITION BY g ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +
+ AFTER MATCH SKIP PAST LAST ROW +
+ INITIAL +
+ PATTERN (start up+) +
+ DEFINE +
+ start AS true, +
+ up AS (val > PREV(val)) );
+(1 row)
+
+SELECT pg_get_viewdef('navns_fn');
+ pg_get_viewdef
+---------------------------------------------------------------------------------------------
+ SELECT id, +
+ count(*) OVER w AS cnt +
+ FROM nt +
+ WINDOW w AS (PARTITION BY g ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +
+ AFTER MATCH SKIP PAST LAST ROW +
+ INITIAL +
+ PATTERN (a+) +
+ DEFINE +
+ a AS (rpr_navns.prev(val) = '-999'::integer) );
+(1 row)
+
+DROP VIEW navns_nav, navns_fn;
+-- Attribute notation is field selection only, never a function fallback
+CREATE TYPE rpr_navns_pair AS (first int, last int);
+CREATE TABLE ct (id int, p rpr_navns_pair);
+INSERT INTO ct VALUES (1, (10, 20)), (2, (30, 40));
+SELECT (p).last FROM ct ORDER BY id;
+ last
+------
+ 20
+ 40
+(2 rows)
+
+SELECT count(*) OVER w FROM ct
+ WINDOW w AS (ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS (p).last > 0);
+ count
+-------
+ 2
+ 0
+(2 rows)
+
+SELECT count(*) OVER w FROM ct
+ WINDOW w AS (ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS (p).prev > 0);
+ERROR: column "prev" not found in data type rpr_navns_pair
+LINE 4: PATTERN (A+) DEFINE A AS (p).prev > 0);
+ ^
+-- Navigation offset must not contain a navigation operation
+SELECT id, val
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+)
+ DEFINE A AS PREV(val, FIRST(1)) > 0)
+ ORDER BY id;
+ERROR: row pattern navigation offset cannot contain a row pattern navigation operation
+LINE 6: DEFINE A AS PREV(val, FIRST(1)) > 0)
+ ^
+DETAIL: A navigation offset must be a run-time constant.
+DROP SCHEMA rpr_navns CASCADE;
+RESET search_path;
-- ============================================================
-- SKIP TO / INITIAL Tests
-- ============================================================
@@ -3655,6 +4018,127 @@ ERROR: cannot nest row pattern navigation more than two levels deep
LINE 6: DEFINE A AS PREV(FIRST(PREV(v))) > 0
^
HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed.
+-- A navigation offset must be a run-time constant, not a navigation operation
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, FIRST(1)) > 0);
+ERROR: row pattern navigation offset cannot contain a row pattern navigation operation
+LINE 4: PATTERN (A+) DEFINE A AS PREV(v, FIRST(1)) > 0);
+ ^
+DETAIL: A navigation offset must be a run-time constant.
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, FIRST(1) + 1) > 0);
+ERROR: row pattern navigation offset cannot contain a row pattern navigation operation
+LINE 4: PATTERN (A+) DEFINE A AS PREV(v, FIRST(1) + 1) > 0);
+ ^
+DETAIL: A navigation offset must be a run-time constant.
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, NEXT(1, 0)) > 0);
+ERROR: row pattern navigation offset cannot contain a row pattern navigation operation
+LINE 4: PATTERN (A+) DEFINE A AS PREV(v, NEXT(1, 0)) > 0);
+ ^
+DETAIL: A navigation offset must be a run-time constant.
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(FIRST(v), LAST(1)) > 0);
+ERROR: row pattern navigation offset cannot contain a row pattern navigation operation
+LINE 4: PATTERN (A+) DEFINE A AS PREV(FIRST(v), LAST(1)) > 0);
+ ^
+DETAIL: A navigation offset must be a run-time constant.
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, FIRST(v)) > 0);
+ERROR: row pattern navigation offset cannot contain a row pattern navigation operation
+LINE 4: PATTERN (A+) DEFINE A AS PREV(v, FIRST(v)) > 0);
+ ^
+DETAIL: A navigation offset must be a run-time constant.
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS NEXT(v, PREV(v, 1)) > 0);
+ERROR: row pattern navigation offset cannot contain a row pattern navigation operation
+LINE 4: PATTERN (A+) DEFINE A AS NEXT(v, PREV(v, 1)) > 0);
+ ^
+DETAIL: A navigation offset must be a run-time constant.
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(FIRST(v, LAST(1)), 2) > 0);
+ERROR: cannot nest row pattern navigation more than two levels deep
+LINE 4: PATTERN (A+) DEFINE A AS PREV(FIRST(v, LAST(1)), 2) > 0)...
+ ^
+HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed.
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, FIRST(1::bigint)) > 0);
+ERROR: row pattern navigation offset cannot contain a row pattern navigation operation
+LINE 4: PATTERN (A+) DEFINE A AS PREV(v, FIRST(1::bigint)) > 0);
+ ^
+DETAIL: A navigation offset must be a run-time constant.
+-- An unknown literal argument resolves to text; it must still reference a column
+SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV('foo') = 'bar');
+ERROR: argument of row pattern navigation operation must include at least one column reference
+LINE 4: PATTERN (A+) DEFINE A AS PREV('foo') = 'bar');
+ ^
+SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV('foo'));
+ERROR: argument of DEFINE must be type boolean, not type text
+LINE 4: PATTERN (A+) DEFINE A AS PREV('foo'));
+ ^
+SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(NULL) IS NULL);
+ERROR: argument of row pattern navigation operation must include at least one column reference
+LINE 4: PATTERN (A+) DEFINE A AS PREV(NULL) IS NULL);
+ ^
+PREPARE rpr_navarg AS SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV($1) IS NULL);
+ERROR: argument of row pattern navigation operation must include at least one column reference
+LINE 4: PATTERN (A+) DEFINE A AS PREV($1) IS NULL);
+ ^
+-- An int2 offset is coerced to int8 like any implicit cast (same as plain 0)
+SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, 0::smallint) = v);
+ count
+-------
+ 5
+ 0
+ 0
+ 0
+ 0
+(5 rows)
+
+SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, 0) = v);
+ count
+-------
+ 5
+ 0
+ 0
+ 0
+ 0
+(5 rows)
+
-- ============================================================
-- Window Deduplication Tests
-- ============================================================
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
index b15b30c85ac..e3e9de789db 100644
--- a/src/test/regress/sql/rpr.sql
+++ b/src/test/regress/sql/rpr.sql
@@ -444,12 +444,6 @@ WINDOW w AS (
-- Error cases: PREV/NEXT usage restrictions
--
--- PREV outside DEFINE clause
-SELECT prev(price) FROM stock;
-
--- NEXT outside DEFINE clause
-SELECT next(price) FROM stock;
-
-- Nested PREV
SELECT price FROM stock
WINDOW w AS (
@@ -831,10 +825,6 @@ WINDOW w AS (
DEFINE A AS PREV(price, 0) = price
);
--- 2-arg PREV/NEXT outside DEFINE clause
-SELECT prev(price, 2) FROM stock;
-SELECT next(price, 2) FROM stock;
-
-- 2-arg PREV/NEXT: negative offset
SELECT company, tdate, price, first_value(price) OVER w
FROM stock
@@ -1124,11 +1114,6 @@ SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS (
DEFINE A AS LAST(val, -1) IS NULL
);
--- FIRST/LAST outside DEFINE clause (error cases)
-SELECT first(val) FROM rpr_nav;
-SELECT last(val) FROM rpr_nav;
-SELECT first(val, 1) FROM rpr_nav;
-
-- Functional notation: should access column, not RPR navigation
CREATE TEMP TABLE rpr_names (prev int, next int, first text, last text);
INSERT INTO rpr_names VALUES (1, 2, 'Joe', 'Blow');
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index fcefd59de4a..e71f0dd3680 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -1309,6 +1309,195 @@ SELECT LAST(id), id, val FROM rpr_nav;
DROP TABLE rpr_nav;
+-- Name-space: prev/next/first/last are navigation functions, not ordinary functions
+CREATE SCHEMA rpr_navns;
+SET search_path TO rpr_navns, public;
+CREATE TABLE nt (g text, id int, val int);
+INSERT INTO nt VALUES ('x', 1, 100), ('x', 2, 200), ('x', 3, 150),
+ ('x', 4, 140), ('x', 5, 150);
+
+-- Outside DEFINE these are ordinary identifiers and resolve to nothing
+SELECT prev(val) FROM nt;
+SELECT next(val) FROM nt;
+SELECT prev(val, 2) FROM nt;
+SELECT next(val, 2) FROM nt;
+SELECT first(val) FROM nt;
+SELECT last(val) FROM nt;
+SELECT first(val, 1) FROM nt;
+-- A schema-qualified call is also a plain (failing) function lookup
+SELECT pg_catalog.prev(val) FROM nt;
+
+-- Outside DEFINE, a user-defined function of that name is callable
+CREATE FUNCTION next(numeric) RETURNS numeric AS 'SELECT -999::numeric'
+ LANGUAGE sql IMMUTABLE;
+SELECT next(10);
+
+-- Inside DEFINE, unqualified PREV is nav whether or not a user prev() exists
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (START UP+)
+ DEFINE START AS TRUE, UP AS val > PREV(val))
+ ORDER BY id;
+
+-- A qualified call invokes the function, so its volatility still matters
+-- VOLATILE: unqualified is nav; qualified is rejected as a volatile function
+CREATE FUNCTION prev(integer) RETURNS integer AS 'SELECT -999'
+ LANGUAGE sql VOLATILE;
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (START UP+)
+ DEFINE START AS TRUE, UP AS val > PREV(val))
+ ORDER BY id;
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+)
+ DEFINE A AS rpr_navns.prev(val) = -999)
+ ORDER BY id;
+DROP FUNCTION prev(integer);
+-- IMMUTABLE: unqualified is nav; qualified is the escape hatch and succeeds
+CREATE FUNCTION prev(integer) RETURNS integer AS 'SELECT -999'
+ LANGUAGE sql IMMUTABLE;
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (START UP+)
+ DEFINE START AS TRUE, UP AS val > PREV(val))
+ ORDER BY id;
+-- (val).prev is attribute notation, so it calls the ordinary function prev(val)
+-- (the IMMUTABLE user prev here), the same as the schema-qualified call below
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+)
+ DEFINE A AS (val).prev = -999)
+ ORDER BY id;
+SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+)
+ DEFINE A AS rpr_navns.prev(val) = -999)
+ ORDER BY id;
+
+-- Zero or more than two arguments is an error, with no function fallback
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV() IS NULL);
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL);
+-- the error stands even when a user function of that exact arity exists
+CREATE FUNCTION prev(integer, integer, integer) RETURNS integer
+ AS 'SELECT -999' LANGUAGE sql IMMUTABLE;
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL);
+DROP FUNCTION prev(integer, integer, integer);
+
+-- Syntactic decoration is rejected
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(*) IS NULL);
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(DISTINCT val) IS NULL);
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val ORDER BY val) IS NULL);
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val) FILTER (WHERE true) IS NULL);
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val) WITHIN GROUP (ORDER BY val) IS NULL);
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val) OVER () IS NULL);
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(VARIADIC ARRAY[val]) IS NULL);
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS prev(x => val) IS NULL);
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS PREV(val) IGNORE NULLS IS NULL);
+
+-- Quoting does not escape: "prev" is nav, "PREV" is an ordinary name
+SELECT id, val, count(*) OVER w AS cnt
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (START UP+)
+ DEFINE START AS TRUE, UP AS val > "prev"(val))
+ ORDER BY id;
+SELECT count(*) OVER w FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS "PREV"(val) IS NULL);
+
+-- A view round-trips: bare PREV stays a navigation function, and a qualified
+-- user prev() stays schema-qualified so it does not reparse as navigation
+CREATE VIEW navns_nav AS
+ SELECT id, count(*) OVER w AS cnt FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (START UP+) DEFINE START AS TRUE, UP AS val > PREV(val));
+CREATE VIEW navns_fn AS
+ SELECT id, count(*) OVER w AS cnt FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS rpr_navns.prev(val) = -999);
+SELECT pg_get_viewdef('navns_nav');
+SELECT pg_get_viewdef('navns_fn');
+DROP VIEW navns_nav, navns_fn;
+
+-- Attribute notation is field selection only, never a function fallback
+CREATE TYPE rpr_navns_pair AS (first int, last int);
+CREATE TABLE ct (id int, p rpr_navns_pair);
+INSERT INTO ct VALUES (1, (10, 20)), (2, (30, 40));
+SELECT (p).last FROM ct ORDER BY id;
+SELECT count(*) OVER w FROM ct
+ WINDOW w AS (ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS (p).last > 0);
+SELECT count(*) OVER w FROM ct
+ WINDOW w AS (ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+) DEFINE A AS (p).prev > 0);
+
+-- Navigation offset must not contain a navigation operation
+SELECT id, val
+ FROM nt
+ WINDOW w AS (PARTITION BY g ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL
+ PATTERN (A+)
+ DEFINE A AS PREV(val, FIRST(1)) > 0)
+ ORDER BY id;
+
+DROP SCHEMA rpr_navns CASCADE;
+RESET search_path;
+
-- ============================================================
-- SKIP TO / INITIAL Tests
-- ============================================================
@@ -2420,6 +2609,68 @@ WINDOW w AS (
DEFINE A AS PREV(FIRST(PREV(v))) > 0
);
+-- A navigation offset must be a run-time constant, not a navigation operation
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, FIRST(1)) > 0);
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, FIRST(1) + 1) > 0);
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, NEXT(1, 0)) > 0);
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(FIRST(v), LAST(1)) > 0);
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, FIRST(v)) > 0);
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS NEXT(v, PREV(v, 1)) > 0);
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(FIRST(v, LAST(1)), 2) > 0);
+SELECT count(*) OVER w
+FROM generate_series(1,10) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, FIRST(1::bigint)) > 0);
+
+-- An unknown literal argument resolves to text; it must still reference a column
+SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV('foo') = 'bar');
+SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV('foo'));
+SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(NULL) IS NULL);
+PREPARE rpr_navarg AS SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV($1) IS NULL);
+
+-- An int2 offset is coerced to int8 like any implicit cast (same as plain 0)
+SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, 0::smallint) = v);
+SELECT count(*) OVER w
+FROM generate_series(1,5) s(v)
+WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+) DEFINE A AS PREV(v, 0) = v);
+
-- ============================================================
-- Window Deduplication Tests
-- ============================================================
--
2.50.1 (Apple Git-155)