Hi
čt 21. 2. 2019 v 3:20 odesílatel Chapman Flack <c...@anastigmatix.net>
napsal:
> The following review has been posted through the commitfest application:
> make installcheck-world: tested, passed
> Implements feature: tested, passed
> Spec compliant: not tested
> Documentation: not tested
>
> Latest patch passes installcheck-world as of d26a810 and does what it sets
> out to do.
>
> I am not sure I have an answer to the objections being raised on grounds
> of taste. To me, it's persuasive that GREATEST and LEAST are described in
> the docco as functions, they are used much like variadic functions, and
> this patch allows them to be used in the ways you would expect variadic
> functions to be usable.
>
> But as to technical readiness, this builds and passes installcheck-world.
> The functions behave as expected (and return null if passed an empty array,
> or an array containing only nulls, or variadic null::foo[]).
>
> Still no corresponding regression tests or doc.
>
> The new status of this patch is: Waiting on Author
>
I wrote doc (just one sentence) and minimal test. Both can be enhanced.
Regards
Pavel
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 86ff4e5c9e..aa4e80cca7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12679,6 +12679,12 @@ SELECT NULLIF(value, '(none)') ...
</synopsis>
<synopsis>
<function>LEAST</function>(<replaceable>value</replaceable> <optional>, ...</optional>)
+</synopsis>
+<synopsis>
+<function>GREATEST</function>(VARIADIC <replaceable>anyarray</replaceable>)
+</synopsis>
+<synopsis>
+<function>LEAST</function>(VARIADIC <replaceable>anyarray</replaceable>)
</synopsis>
<para>
@@ -12697,6 +12703,11 @@ SELECT NULLIF(value, '(none)') ...
make them return NULL if any argument is NULL, rather than only when
all are NULL.
</para>
+
+ <para>
+ These functions supports passing parameters as a array after
+ <literal>VARIADIC</literal> keyword.
+ </para>
</sect2>
</sect1>
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index db3777d15e..01d7f0e02c 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -1854,13 +1854,18 @@ ExecInitExprRec(Expr *node, ExprState *state,
case T_MinMaxExpr:
{
MinMaxExpr *minmaxexpr = (MinMaxExpr *) node;
- int nelems = list_length(minmaxexpr->args);
+ int nelems;
TypeCacheEntry *typentry;
FmgrInfo *finfo;
FunctionCallInfo fcinfo;
ListCell *lc;
int off;
+ if (minmaxexpr->args)
+ nelems = list_length(minmaxexpr->args);
+ else
+ nelems = 1;
+
/* Look up the btree comparison function for the datatype */
typentry = lookup_type_cache(minmaxexpr->minmaxtype,
TYPECACHE_CMP_PROC);
@@ -1897,16 +1902,29 @@ ExecInitExprRec(Expr *node, ExprState *state,
scratch.d.minmax.finfo = finfo;
scratch.d.minmax.fcinfo_data = fcinfo;
- /* evaluate expressions into minmax->values/nulls */
- off = 0;
- foreach(lc, minmaxexpr->args)
+ if (minmaxexpr->args)
{
- Expr *e = (Expr *) lfirst(lc);
+ scratch.d.minmax.variadic_arg = false;
- ExecInitExprRec(e, state,
- &scratch.d.minmax.values[off],
- &scratch.d.minmax.nulls[off]);
- off++;
+ /* evaluate expressions into minmax->values/nulls */
+ off = 0;
+ foreach(lc, minmaxexpr->args)
+ {
+ Expr *e = (Expr *) lfirst(lc);
+
+ ExecInitExprRec(e, state,
+ &scratch.d.minmax.values[off],
+ &scratch.d.minmax.nulls[off]);
+ off++;
+ }
+ }
+ else
+ {
+ scratch.d.minmax.variadic_arg = true;
+
+ ExecInitExprRec(minmaxexpr->variadic_arg, state,
+ &scratch.d.minmax.values[0],
+ &scratch.d.minmax.nulls[0]);
}
/* and push the final comparison */
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index a018925d4e..748c950885 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -2819,6 +2819,55 @@ ExecEvalMinMax(ExprState *state, ExprEvalStep *op)
/* default to null result */
*op->resnull = true;
+ if (op->d.minmax.variadic_arg)
+ {
+ ArrayIterator array_iterator;
+ ArrayType *arr;
+ Datum value;
+ bool isnull;
+
+ if (nulls[0])
+ return;
+
+ arr = DatumGetArrayTypeP(values[0]);
+
+ array_iterator = array_create_iterator(arr, 0, NULL);
+ while (array_iterate(array_iterator, &value, &isnull))
+ {
+ if (isnull)
+ continue;
+
+ if (*op->resnull)
+ {
+ /* first nonnull input */
+ *op->resvalue = value;
+ *op->resnull = false;
+ }
+ else
+ {
+ int cmpresult;
+
+ Assert(fcinfo->nargs == 2);
+
+ /* apply comparison function */
+ fcinfo->args[0].value = *op->resvalue;
+ fcinfo->args[1].value = value;
+
+ fcinfo->isnull = false;
+ cmpresult = DatumGetInt32(FunctionCallInvoke(fcinfo));
+ if (fcinfo->isnull) /* probably should not happen */
+ continue;
+
+ if (cmpresult > 0 && operator == IS_LEAST)
+ *op->resvalue = value;
+ else if (cmpresult < 0 && operator == IS_GREATEST)
+ *op->resvalue = value;
+ }
+ }
+
+ return;
+ }
+
for (off = 0; off < op->d.minmax.nelems; off++)
{
/* ignore NULL inputs */
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index e15724bb0e..e8ad89799f 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1942,6 +1942,7 @@ _copyMinMaxExpr(const MinMaxExpr *from)
COPY_SCALAR_FIELD(inputcollid);
COPY_SCALAR_FIELD(op);
COPY_NODE_FIELD(args);
+ COPY_NODE_FIELD(variadic_arg);
COPY_LOCATION_FIELD(location);
return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 31499eb798..6623c7800d 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -635,6 +635,7 @@ _equalMinMaxExpr(const MinMaxExpr *a, const MinMaxExpr *b)
COMPARE_SCALAR_FIELD(inputcollid);
COMPARE_SCALAR_FIELD(op);
COMPARE_NODE_FIELD(args);
+ COMPARE_NODE_FIELD(variadic_arg);
COMPARE_LOCATION_FIELD(location);
return true;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 8ed30c011a..52fcf8d9b0 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -465,6 +465,9 @@ exprTypmod(const Node *expr)
int32 typmod;
ListCell *arg;
+ if (mexpr->variadic_arg)
+ return exprTypmod((Node *) mexpr->variadic_arg);
+
if (exprType((Node *) linitial(mexpr->args)) != minmaxtype)
return -1;
typmod = exprTypmod((Node *) linitial(mexpr->args));
@@ -2072,7 +2075,15 @@ expression_tree_walker(Node *node,
case T_CoalesceExpr:
return walker(((CoalesceExpr *) node)->args, context);
case T_MinMaxExpr:
- return walker(((MinMaxExpr *) node)->args, context);
+ {
+ MinMaxExpr *mexpr = (MinMaxExpr *) node;
+
+ if (walker(mexpr->args, context))
+ return true;
+ if (walker(mexpr->variadic_arg, context))
+ return true;
+ }
+ break;
case T_XmlExpr:
{
XmlExpr *xexpr = (XmlExpr *) node;
@@ -2839,6 +2850,7 @@ expression_tree_mutator(Node *node,
FLATCOPY(newnode, minmaxexpr, MinMaxExpr);
MUTATE(newnode->args, minmaxexpr->args, List *);
+ MUTATE(newnode->variadic_arg, minmaxexpr->variadic_arg, Expr *);
return (Node *) newnode;
}
break;
@@ -3369,7 +3381,15 @@ raw_expression_tree_walker(Node *node,
case T_CoalesceExpr:
return walker(((CoalesceExpr *) node)->args, context);
case T_MinMaxExpr:
- return walker(((MinMaxExpr *) node)->args, context);
+ {
+ MinMaxExpr *mexpr = (MinMaxExpr *) node;
+
+ if (walker(mexpr->args, context))
+ return true;
+ if (walker(mexpr->variadic_arg, context))
+ return true;
+ }
+ break;
case T_XmlExpr:
{
XmlExpr *xexpr = (XmlExpr *) node;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 65302fe65b..531aa07b61 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1498,6 +1498,7 @@ _outMinMaxExpr(StringInfo str, const MinMaxExpr *node)
WRITE_OID_FIELD(inputcollid);
WRITE_ENUM_FIELD(op, MinMaxOp);
WRITE_NODE_FIELD(args);
+ WRITE_NODE_FIELD(variadic_arg);
WRITE_LOCATION_FIELD(location);
}
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 5aa42242a9..14641ddf51 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1088,6 +1088,7 @@ _readMinMaxExpr(void)
READ_OID_FIELD(inputcollid);
READ_ENUM_FIELD(op, MinMaxOp);
READ_NODE_FIELD(args);
+ READ_NODE_FIELD(variadic_arg);
READ_LOCATION_FIELD(location);
READ_DONE();
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a68f78e0e0..975eeb60ff 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -13803,6 +13803,22 @@ func_expr_common_subexpr:
v->location = @1;
$$ = (Node *)v;
}
+ | GREATEST '(' VARIADIC a_expr ')'
+ {
+ MinMaxExpr *v = makeNode(MinMaxExpr);
+ v->variadic_arg = (Expr *) $4;
+ v->op = IS_GREATEST;
+ v->location = @1;
+ $$ = (Node *)v;
+ }
+ | LEAST '(' VARIADIC a_expr ')'
+ {
+ MinMaxExpr *v = makeNode(MinMaxExpr);
+ v->variadic_arg = (Expr *) $4;
+ v->op = IS_LEAST;
+ v->location = @1;
+ $$ = (Node *)v;
+ }
| XMLCONCAT '(' expr_list ')'
{
$$ = makeXmlExpr(IS_XMLCONCAT, NULL, NIL, $3, @1);
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index e559353529..711de0b5b5 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -2263,35 +2263,60 @@ transformMinMaxExpr(ParseState *pstate, MinMaxExpr *m)
MinMaxExpr *newm = makeNode(MinMaxExpr);
List *newargs = NIL;
List *newcoercedargs = NIL;
- const char *funcname = (m->op == IS_GREATEST) ? "GREATEST" : "LEAST";
- ListCell *args;
newm->op = m->op;
- foreach(args, m->args)
+
+ if (m->args)
{
- Node *e = (Node *) lfirst(args);
- Node *newe;
+ ListCell *args;
+ const char *funcname = (m->op == IS_GREATEST) ? "GREATEST" : "LEAST";
- newe = transformExprRecurse(pstate, e);
- newargs = lappend(newargs, newe);
- }
+ foreach(args, m->args)
+ {
+ Node *e = (Node *) lfirst(args);
+ Node *newe;
- newm->minmaxtype = select_common_type(pstate, newargs, funcname, NULL);
- /* minmaxcollid and inputcollid will be set by parse_collate.c */
+ newe = transformExprRecurse(pstate, e);
+ newargs = lappend(newargs, newe);
+ }
- /* Convert arguments if necessary */
- foreach(args, newargs)
+ newm->minmaxtype = select_common_type(pstate, newargs, funcname, NULL);
+ /* minmaxcollid and inputcollid will be set by parse_collate.c */
+
+ /* Convert arguments if necessary */
+ foreach(args, newargs)
+ {
+ Node *e = (Node *) lfirst(args);
+ Node *newe;
+
+ newe = coerce_to_common_type(pstate, e,
+ newm->minmaxtype,
+ funcname);
+ newcoercedargs = lappend(newcoercedargs, newe);
+ }
+
+ newm->args = newcoercedargs;
+ }
+ else
{
- Node *e = (Node *) lfirst(args);
- Node *newe;
+ Oid input_type;
+ Oid element_typeid;
- newe = coerce_to_common_type(pstate, e,
- newm->minmaxtype,
- funcname);
- newcoercedargs = lappend(newcoercedargs, newe);
+ Assert(m->variadic_arg);
+
+ newm->variadic_arg = (Expr *) transformExprRecurse(pstate, (Node *) m->variadic_arg);
+ input_type = exprType((Node *) newm->variadic_arg);
+
+ if (input_type == InvalidOid)
+ elog(ERROR, "cannot to determinate result type");
+
+ element_typeid = get_base_element_type(input_type);
+ if (!OidIsValid(element_typeid))
+ elog(ERROR, "expected a array parameter");
+
+ newm->minmaxtype = element_typeid;
}
- newm->args = newcoercedargs;
newm->location = m->location;
return (Node *) newm;
}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 1258092dc8..3431b39d53 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8474,7 +8474,17 @@ get_rule_expr(Node *node, deparse_context *context,
appendStringInfoString(buf, "LEAST(");
break;
}
- get_rule_expr((Node *) minmaxexpr->args, context, true);
+
+ if (minmaxexpr->args)
+ {
+ get_rule_expr((Node *) minmaxexpr->args, context, true);
+ }
+ else
+ {
+ appendStringInfoString(buf, "VARIADIC ");
+ get_rule_expr((Node *) minmaxexpr->variadic_arg, context, true);
+ }
+
appendStringInfoChar(buf, ')');
}
break;
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index 7aacdc5d04..7b74f0d9f3 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -459,6 +459,7 @@ typedef struct ExprEvalStep
/* workspace for argument values */
Datum *values;
bool *nulls;
+ bool variadic_arg;
int nelems;
/* is it GREATEST or LEAST? */
MinMaxOp op;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index a7efae7038..4a5a2a4b5a 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1091,6 +1091,7 @@ typedef struct MinMaxExpr
Oid inputcollid; /* OID of collation that function should use */
MinMaxOp op; /* function to execute */
List *args; /* the arguments */
+ Expr *variadic_arg; /* the variadic argument */
int location; /* token location, or -1 if unknown */
} MinMaxExpr;
diff --git a/src/test/regress/expected/case.out b/src/test/regress/expected/case.out
index c0c8acf035..eca127ed2d 100644
--- a/src/test/regress/expected/case.out
+++ b/src/test/regress/expected/case.out
@@ -392,3 +392,54 @@ ROLLBACK;
--
DROP TABLE CASE_TBL;
DROP TABLE CASE2_TBL;
+--
+-- greatest and least tests
+--
+SELECT least(10,20,30,40);
+ least
+-------
+ 10
+(1 row)
+
+SELECT greatest(10,20,30,40);
+ greatest
+----------
+ 40
+(1 row)
+
+SELECT least(VARIADIC ARRAY[10,20,30,40]);
+ least
+-------
+ 10
+(1 row)
+
+SELECT greatest(VARIADIC ARRAY[10,20,30,40]);
+ greatest
+----------
+ 40
+(1 row)
+
+SELECT least(10,20,30,40, NULL);
+ least
+-------
+ 10
+(1 row)
+
+SELECT greatest(10,20,30,40, NULL);
+ greatest
+----------
+ 40
+(1 row)
+
+SELECT least(VARIADIC ARRAY[10,20,30,40, NULL]);
+ least
+-------
+ 10
+(1 row)
+
+SELECT greatest(VARIADIC ARRAY[10,20,30,40, NULL]);
+ greatest
+----------
+ 40
+(1 row)
+
diff --git a/src/test/regress/sql/case.sql b/src/test/regress/sql/case.sql
index 17436c524a..f79c4d4efc 100644
--- a/src/test/regress/sql/case.sql
+++ b/src/test/regress/sql/case.sql
@@ -252,3 +252,18 @@ ROLLBACK;
DROP TABLE CASE_TBL;
DROP TABLE CASE2_TBL;
+
+--
+-- greatest and least tests
+--
+SELECT least(10,20,30,40);
+SELECT greatest(10,20,30,40);
+SELECT least(VARIADIC ARRAY[10,20,30,40]);
+SELECT greatest(VARIADIC ARRAY[10,20,30,40]);
+
+SELECT least(10,20,30,40, NULL);
+SELECT greatest(10,20,30,40, NULL);
+SELECT least(VARIADIC ARRAY[10,20,30,40, NULL]);
+SELECT greatest(VARIADIC ARRAY[10,20,30,40, NULL]);
+
+
