Skip to content

Commit 74aea2a

Browse files
committed
Support default arguments and named-argument notation for window functions.
These things didn't work because the planner omitted to do the necessary preprocessing of a WindowFunc's argument list. Add the few dozen lines of code needed to handle that. Although this sounds like a feature addition, it's really a bug fix because the default-argument case was likely to crash previously, due to lack of checking of the number of supplied arguments in the built-in window functions. It's not a security issue because there's no way for a non-superuser to create a window function definition with defaults that refers to a built-in C function, but nonetheless people might be annoyed that it crashes rather than producing a useful error message. So back-patch as far as the patch applies easily, which turns out to be 9.2. I'll put a band-aid in earlier versions as a separate patch. (Note that these features still don't work for aggregates, and fixing that case will be harder since we represent aggregate arg lists as target lists not bare expression lists. There's no crash risk though because CREATE AGGREGATE doesn't accept defaults, and we reject named-argument notation when parsing an aggregate call.)
1 parent 599942c commit 74aea2a

File tree

6 files changed

+102
-13
lines changed

6 files changed

+102
-13
lines changed

doc/src/sgml/syntax.sgml

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2464,6 +2464,14 @@ SELECT concat_lower_or_upper('Hello', 'World', uppercase := true);
24642464
having numerous parameters that have default values, named or mixed
24652465
notation can save a great deal of writing and reduce chances for error.
24662466
</para>
2467+
2468+
<note>
2469+
<para>
2470+
Named and mixed call notations currently cannot be used when calling an
2471+
aggregate function (but they do work when an aggregate function is used
2472+
as a window function).
2473+
</para>
2474+
</note>
24672475
</sect2>
24682476
</sect1>
24692477

src/backend/optimizer/util/clauses.c

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2293,6 +2293,50 @@ eval_const_expressions_mutator(Node *node,
22932293
*/
22942294
return (Node *) copyObject(param);
22952295
}
2296+
case T_WindowFunc:
2297+
{
2298+
WindowFunc *expr = (WindowFunc *) node;
2299+
Oid funcid = expr->winfnoid;
2300+
List *args;
2301+
HeapTuple func_tuple;
2302+
WindowFunc *newexpr;
2303+
2304+
/*
2305+
* We can't really simplify a WindowFunc node, but we mustn't
2306+
* just fall through to the default processing, because we
2307+
* have to apply expand_function_arguments to its argument
2308+
* list. That takes care of inserting default arguments and
2309+
* expanding named-argument notation.
2310+
*/
2311+
func_tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid));
2312+
if (!HeapTupleIsValid(func_tuple))
2313+
elog(ERROR, "cache lookup failed for function %u", funcid);
2314+
2315+
args = expand_function_arguments(expr->args, expr->wintype,
2316+
func_tuple);
2317+
2318+
ReleaseSysCache(func_tuple);
2319+
2320+
/* Now, recursively simplify the args (which are a List) */
2321+
args = (List *)
2322+
expression_tree_mutator((Node *) args,
2323+
eval_const_expressions_mutator,
2324+
(void *) context);
2325+
2326+
/* And build the replacement WindowFunc node */
2327+
newexpr = makeNode(WindowFunc);
2328+
newexpr->winfnoid = expr->winfnoid;
2329+
newexpr->wintype = expr->wintype;
2330+
newexpr->wincollid = expr->wincollid;
2331+
newexpr->inputcollid = expr->inputcollid;
2332+
newexpr->args = args;
2333+
newexpr->winref = expr->winref;
2334+
newexpr->winstar = expr->winstar;
2335+
newexpr->winagg = expr->winagg;
2336+
newexpr->location = expr->location;
2337+
2338+
return (Node *) newexpr;
2339+
}
22962340
case T_FuncExpr:
22972341
{
22982342
FuncExpr *expr = (FuncExpr *) node;

src/backend/parser/parse_func.c

Lines changed: 0 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -494,17 +494,6 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
494494
errmsg("window functions cannot return sets"),
495495
parser_errposition(pstate, location)));
496496

497-
/*
498-
* We might want to support this later, but for now reject it because
499-
* the planner and executor wouldn't cope with NamedArgExprs in a
500-
* WindowFunc node.
501-
*/
502-
if (argnames != NIL)
503-
ereport(ERROR,
504-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
505-
errmsg("window functions cannot use named arguments"),
506-
parser_errposition(pstate, location)));
507-
508497
/* parse_agg.c does additional window-func-specific processing */
509498
transformWindowFuncCall(pstate, wfunc, over);
510499

src/backend/utils/adt/ruleutils.c

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6131,25 +6131,28 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
61316131
StringInfo buf = context->buf;
61326132
Oid argtypes[FUNC_MAX_ARGS];
61336133
int nargs;
6134+
List *argnames;
61346135
ListCell *l;
61356136

61366137
if (list_length(wfunc->args) > FUNC_MAX_ARGS)
61376138
ereport(ERROR,
61386139
(errcode(ERRCODE_TOO_MANY_ARGUMENTS),
61396140
errmsg("too many arguments")));
61406141
nargs = 0;
6142+
argnames = NIL;
61416143
foreach(l, wfunc->args)
61426144
{
61436145
Node *arg = (Node *) lfirst(l);
61446146

6145-
Assert(!IsA(arg, NamedArgExpr));
6147+
if (IsA(arg, NamedArgExpr))
6148+
argnames = lappend(argnames, ((NamedArgExpr *) arg)->name);
61466149
argtypes[nargs] = exprType(arg);
61476150
nargs++;
61486151
}
61496152

61506153
appendStringInfo(buf, "%s(",
61516154
generate_function_name(wfunc->winfnoid, nargs,
6152-
NIL, argtypes, NULL));
6155+
argnames, argtypes, NULL));
61536156
/* winstar can be set only in zero-argument aggregates */
61546157
if (wfunc->winstar)
61556158
appendStringInfoChar(buf, '*');

src/test/regress/expected/window.out

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1020,3 +1020,38 @@ SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
10201020
ERROR: argument of nth_value must be greater than zero
10211021
-- cleanup
10221022
DROP TABLE empsalary;
1023+
-- test user-defined window function with named args and default args
1024+
CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement
1025+
LANGUAGE internal WINDOW IMMUTABLE STRICT AS 'window_nth_value';
1026+
SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four
1027+
FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;
1028+
nth_value_def | ten | four
1029+
---------------+-----+------
1030+
0 | 0 | 0
1031+
0 | 0 | 0
1032+
0 | 4 | 0
1033+
1 | 1 | 1
1034+
1 | 1 | 1
1035+
1 | 7 | 1
1036+
1 | 9 | 1
1037+
| 0 | 2
1038+
3 | 1 | 3
1039+
3 | 3 | 3
1040+
(10 rows)
1041+
1042+
SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four
1043+
FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;
1044+
nth_value_def | ten | four
1045+
---------------+-----+------
1046+
0 | 0 | 0
1047+
0 | 0 | 0
1048+
0 | 4 | 0
1049+
1 | 1 | 1
1050+
1 | 1 | 1
1051+
1 | 7 | 1
1052+
1 | 9 | 1
1053+
0 | 0 | 2
1054+
1 | 1 | 3
1055+
1 | 3 | 3
1056+
(10 rows)
1057+

src/test/regress/sql/window.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -266,3 +266,13 @@ SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
266266

267267
-- cleanup
268268
DROP TABLE empsalary;
269+
270+
-- test user-defined window function with named args and default args
271+
CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement
272+
LANGUAGE internal WINDOW IMMUTABLE STRICT AS 'window_nth_value';
273+
274+
SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four
275+
FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;
276+
277+
SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four
278+
FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;

0 commit comments

Comments
 (0)