On Fri, Jun 28, 2013 at 09:22:52PM +0100, Dean Rasheed wrote:
> On 21 June 2013 06:16, David Fetter <da...@fetter.org> wrote:
> > Please find attached a patch which allows subqueries in the FILTER
> > clause and adds regression testing for same.
> >
>
> This needs re-basing/merging following Robert's recent commit to make
> OVER unreserved.
Please find attached. Thanks, Andrew Gierth! In this one, FILTER is
no longer a reserved word.
Cheers,
David.
--
David Fetter <da...@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml
index 5e3b33a..ecfde99 100644
--- a/doc/src/sgml/keywords.sgml
+++ b/doc/src/sgml/keywords.sgml
@@ -1786,7 +1786,7 @@
</row>
<row>
<entry><token>FILTER</token></entry>
- <entry></entry>
+ <entry>non-reserved</entry>
<entry>reserved</entry>
<entry>reserved</entry>
<entry></entry>
@@ -3200,7 +3200,7 @@
</row>
<row>
<entry><token>OVER</token></entry>
- <entry>reserved (can be function or type)</entry>
+ <entry>non-reserved</entry>
<entry>reserved</entry>
<entry>reserved</entry>
<entry></entry>
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 68309ba..b289a3a 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -594,10 +594,13 @@ GROUP BY <replaceable
class="parameter">expression</replaceable> [, ...]
</para>
<para>
- Aggregate functions, if any are used, are computed across all rows
+ In the absence of a <literal>FILTER</literal> clause,
+ aggregate functions, if any are used, are computed across all rows
making up each group, producing a separate value for each group
(whereas without <literal>GROUP BY</literal>, an aggregate
produces a single value computed across all the selected rows).
+ When a <literal>FILTER</literal> clause is present, only those
+ rows matching the FILTER clause are included.
When <literal>GROUP BY</literal> is present, it is not valid for
the <command>SELECT</command> list expressions to refer to
ungrouped columns except within aggregate functions or if the
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index b139212..c4d5f33 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1562,24 +1562,26 @@ sqrt(2)
syntax of an aggregate expression is one of the following:
<synopsis>
-<replaceable>aggregate_name</replaceable>
(<replaceable>expression</replaceable> [ , ... ] [
<replaceable>order_by_clause</replaceable> ] )
-<replaceable>aggregate_name</replaceable> (ALL
<replaceable>expression</replaceable> [ , ... ] [
<replaceable>order_by_clause</replaceable> ] )
-<replaceable>aggregate_name</replaceable> (DISTINCT
<replaceable>expression</replaceable> [ , ... ] [
<replaceable>order_by_clause</replaceable> ] )
-<replaceable>aggregate_name</replaceable> ( * )
+<replaceable>aggregate_name</replaceable>
(<replaceable>expression</replaceable> [ , ... ] [
<replaceable>order_by_clause</replaceable> ] ) [ FILTER ( WHERE
<replaceable>filter_clause</replaceable> ) ]
+<replaceable>aggregate_name</replaceable> (ALL
<replaceable>expression</replaceable> [ , ... ] [
<replaceable>order_by_clause</replaceable> ] ) [ FILTER ( WHERE
<replaceable>filter_clause</replaceable> ) ]
+<replaceable>aggregate_name</replaceable> (DISTINCT
<replaceable>expression</replaceable> [ , ... ] [
<replaceable>order_by_clause</replaceable> ] ) [ FILTER ( WHERE
<replaceable>filter_clause</replaceable> ) ]
+<replaceable>aggregate_name</replaceable> ( * ) [ FILTER ( WHERE
<replaceable>filter_clause</replaceable> ) ]
</synopsis>
where <replaceable>aggregate_name</replaceable> is a previously
defined aggregate (possibly qualified with a schema name),
- <replaceable>expression</replaceable> is
- any value expression that does not itself contain an aggregate
- expression or a window function call, and
- <replaceable>order_by_clause</replaceable> is a optional
- <literal>ORDER BY</> clause as described below.
+ <replaceable>expression</replaceable> is any value expression that
+ does not itself contain an aggregate expression or a window
+ function call, <replaceable>order_by_clause</replaceable> is a
+ optional <literal>ORDER BY</> clause as described below. The
+ <replaceable>aggregate_name</replaceable> can also be suffixed
+ with <literal>FILTER</literal> as described below.
</para>
<para>
- The first form of aggregate expression invokes the aggregate
- once for each input row.
+ The first form of aggregate expression invokes the aggregate once
+ for each input row, or when a FILTER clause is present, each row
+ matching same.
The second form is the same as the first, since
<literal>ALL</literal> is the default.
The third form invokes the aggregate once for each distinct value
@@ -1607,6 +1609,21 @@ sqrt(2)
</para>
<para>
+ Adding a FILTER clause to an aggregate specifies which values of
+ the expression being aggregated to evaluate. For example:
+<programlisting>
+SELECT
+ count(*) AS unfiltered,
+ count(*) FILTER (WHERE i < 5) AS filtered
+FROM generate_series(1,10) AS s(i);
+ unfiltered | filtered
+------------+----------
+ 10 | 4
+(1 row)
+</programlisting>
+ </para>
+
+ <para>
Ordinarily, the input rows are fed to the aggregate function in an
unspecified order. In many cases this does not matter; for example,
<function>min</> produces the same result no matter what order it
@@ -1709,10 +1726,10 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; --
incorrect
The syntax of a window function call is one of the following:
<synopsis>
-<replaceable>function_name</replaceable>
(<optional><replaceable>expression</replaceable> <optional>,
<replaceable>expression</replaceable> ... </optional></optional>) OVER (
<replaceable class="parameter">window_definition</replaceable> )
-<replaceable>function_name</replaceable>
(<optional><replaceable>expression</replaceable> <optional>,
<replaceable>expression</replaceable> ... </optional></optional>) OVER
<replaceable>window_name</replaceable>
-<replaceable>function_name</replaceable> ( * ) OVER ( <replaceable
class="parameter">window_definition</replaceable> )
-<replaceable>function_name</replaceable> ( * ) OVER
<replaceable>window_name</replaceable>
+<replaceable>function_name</replaceable>
(<optional><replaceable>expression</replaceable> <optional>,
<replaceable>expression</replaceable> ... </optional></optional>) [ FILTER (
WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable
class="parameter">window_definition</replaceable> )
+<replaceable>function_name</replaceable>
(<optional><replaceable>expression</replaceable> <optional>,
<replaceable>expression</replaceable> ... </optional></optional>) [ FILTER (
WHERE <replaceable>filter_clause</replaceable> ) ] OVER
<replaceable>window_name</replaceable>
+<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE
<replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable
class="parameter">window_definition</replaceable> )
+<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE
<replaceable>filter_clause</replaceable> ) ] OVER
<replaceable>window_name</replaceable>
</synopsis>
where <replaceable class="parameter">window_definition</replaceable>
has the syntax
@@ -1836,16 +1853,18 @@ UNBOUNDED FOLLOWING
The built-in window functions are described in <xref
linkend="functions-window-table">. Other window functions can be added by
the user. Also, any built-in or user-defined aggregate function can be
- used as a window function.
+ used as a window function. A <literal>FILTER</literal> clause is
+ only valid for aggregate functions used in windowing.
</para>
<para>
- The syntaxes using <literal>*</> are used for calling parameter-less
- aggregate functions as window functions, for example
- <literal>count(*) OVER (PARTITION BY x ORDER BY y)</>.
- The asterisk (<literal>*</>) is customarily not used for non-aggregate
window functions.
- Aggregate window functions, unlike normal aggregate functions, do not
- allow <literal>DISTINCT</> or <literal>ORDER BY</> to be used within the
+ The syntaxes using <literal>*</> are used for calling
+ parameter-less aggregate functions as window functions, for
+ example <literal>count(*) OVER (PARTITION BY x ORDER BY y)</>.
+ The asterisk (<literal>*</>) is customarily not used for
+ non-aggregate window functions. Aggregate window functions,
+ unlike normal aggregate functions, do not allow
+ <literal>DISTINCT</> or <literal>ORDER BY</> to be used within the
function argument list.
</para>
diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c
index 1388183..34dbef9 100644
--- a/src/backend/executor/execQual.c
+++ b/src/backend/executor/execQual.c
@@ -4410,6 +4410,7 @@ ExecInitExpr(Expr *node, PlanState *parent)
astate->args = (List *)
ExecInitExpr((Expr *) aggref->args,
parent);
+ astate->agg_filter =
ExecInitExpr(aggref->agg_filter, parent);
/*
* Complain if the aggregate's
arguments contain any
@@ -4448,6 +4449,7 @@ ExecInitExpr(Expr *node, PlanState *parent)
wfstate->args = (List *)
ExecInitExpr((Expr *) wfunc->args,
parent);
+ wfstate->agg_filter =
ExecInitExpr(wfunc->agg_filter, parent);
/*
* Complain if the windowfunc's
arguments contain any
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 12e1b8e..a43fdf2 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -381,7 +381,7 @@ sql_fn_post_column_ref(ParseState *pstate, ColumnRef *cref,
Node *var)
list_make1(subfield),
list_make1(param),
NIL, false,
false, false,
- NULL, true,
cref->location);
+ NULL, NULL,
true, cref->location);
}
return param;
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index c741131..19105d2 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -488,6 +488,18 @@ advance_aggregates(AggState *aggstate, AggStatePerGroup
pergroup)
int i;
TupleTableSlot *slot;
+ /* Skip anything FILTERed out */
+ ExprState *filter = peraggstate->aggrefstate->agg_filter;
+ if (filter)
+ {
+ MemoryContext oldcontext =
MemoryContextSwitchTo(aggstate->tmpcontext->ecxt_per_tuple_memory);
+ bool isnull;
+ Datum res = ExecEvalExpr(filter, aggstate->tmpcontext,
&isnull, NULL);
+ MemoryContextSwitchTo(oldcontext);
+ if (isnull || !DatumGetBool(res))
+ continue;
+ }
+
/* Evaluate the current input expressions for this aggregate */
slot = ExecProject(peraggstate->evalproj, NULL);
diff --git a/src/backend/executor/nodeWindowAgg.c
b/src/backend/executor/nodeWindowAgg.c
index d9f0e79..c00b058 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -227,9 +227,22 @@ advance_windowaggregate(WindowAggState *winstate,
int i;
MemoryContext oldContext;
ExprContext *econtext = winstate->tmpcontext;
+ ExprState *filter = wfuncstate->agg_filter;
oldContext = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);
+ /* Skip anything FILTERed out */
+ if (filter)
+ {
+ bool isnull;
+ Datum res = ExecEvalExpr(filter, econtext, &isnull, NULL);
+ if (isnull || !DatumGetBool(res))
+ {
+ MemoryContextSwitchTo(oldContext);
+ return;
+ }
+ }
+
/* We start from 1, since the 0th arg will be the transition value */
i = 1;
foreach(arg, wfuncstate->args)
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index b5b8d63..050fc83 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1137,6 +1137,7 @@ _copyAggref(const Aggref *from)
COPY_NODE_FIELD(args);
COPY_NODE_FIELD(aggorder);
COPY_NODE_FIELD(aggdistinct);
+ COPY_NODE_FIELD(agg_filter);
COPY_SCALAR_FIELD(aggstar);
COPY_SCALAR_FIELD(agglevelsup);
COPY_LOCATION_FIELD(location);
@@ -1157,6 +1158,7 @@ _copyWindowFunc(const WindowFunc *from)
COPY_SCALAR_FIELD(wincollid);
COPY_SCALAR_FIELD(inputcollid);
COPY_NODE_FIELD(args);
+ COPY_NODE_FIELD(agg_filter);
COPY_SCALAR_FIELD(winref);
COPY_SCALAR_FIELD(winstar);
COPY_SCALAR_FIELD(winagg);
@@ -2155,6 +2157,7 @@ _copyFuncCall(const FuncCall *from)
COPY_SCALAR_FIELD(agg_star);
COPY_SCALAR_FIELD(agg_distinct);
COPY_SCALAR_FIELD(func_variadic);
+ COPY_NODE_FIELD(agg_filter);
COPY_NODE_FIELD(over);
COPY_LOCATION_FIELD(location);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 3f96595..e1f63f1 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -196,6 +196,7 @@ _equalAggref(const Aggref *a, const Aggref *b)
COMPARE_NODE_FIELD(args);
COMPARE_NODE_FIELD(aggorder);
COMPARE_NODE_FIELD(aggdistinct);
+ COMPARE_NODE_FIELD(agg_filter);
COMPARE_SCALAR_FIELD(aggstar);
COMPARE_SCALAR_FIELD(agglevelsup);
COMPARE_LOCATION_FIELD(location);
@@ -211,6 +212,7 @@ _equalWindowFunc(const WindowFunc *a, const WindowFunc *b)
COMPARE_SCALAR_FIELD(wincollid);
COMPARE_SCALAR_FIELD(inputcollid);
COMPARE_NODE_FIELD(args);
+ COMPARE_NODE_FIELD(agg_filter);
COMPARE_SCALAR_FIELD(winref);
COMPARE_SCALAR_FIELD(winstar);
COMPARE_SCALAR_FIELD(winagg);
@@ -1995,6 +1997,7 @@ _equalFuncCall(const FuncCall *a, const FuncCall *b)
COMPARE_SCALAR_FIELD(agg_star);
COMPARE_SCALAR_FIELD(agg_distinct);
COMPARE_SCALAR_FIELD(func_variadic);
+ COMPARE_NODE_FIELD(agg_filter);
COMPARE_NODE_FIELD(over);
COMPARE_LOCATION_FIELD(location);
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 42d6621..d5b4049 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1570,6 +1570,8 @@ expression_tree_walker(Node *node,
if (expression_tree_walker((Node *)
expr->aggdistinct,
walker, context))
return true;
+ if (walker((Node *) expr->agg_filter, context))
+ return true;
}
break;
case T_WindowFunc:
@@ -1580,6 +1582,8 @@ expression_tree_walker(Node *node,
if (expression_tree_walker((Node *) expr->args,
walker, context))
return true;
+ if (walker((Node *) expr->agg_filter, context))
+ return true;
}
break;
case T_ArrayRef:
@@ -2079,6 +2083,7 @@ expression_tree_mutator(Node *node,
MUTATE(newnode->args, aggref->args, List *);
MUTATE(newnode->aggorder, aggref->aggorder,
List *);
MUTATE(newnode->aggdistinct,
aggref->aggdistinct, List *);
+ MUTATE(newnode->agg_filter, aggref->agg_filter,
Expr *);
return (Node *) newnode;
}
break;
@@ -2089,6 +2094,7 @@ expression_tree_mutator(Node *node,
FLATCOPY(newnode, wfunc, WindowFunc);
MUTATE(newnode->args, wfunc->args, List *);
+ MUTATE(newnode->agg_filter, wfunc->agg_filter,
Expr *);
return (Node *) newnode;
}
break;
@@ -2951,6 +2957,8 @@ raw_expression_tree_walker(Node *node,
return true;
if (walker(fcall->agg_order, context))
return true;
+ if (walker(fcall->agg_filter, context))
+ return true;
if (walker(fcall->over, context))
return true;
/* function name is deemed uninteresting */
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index b2183f4..cc09a9a 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -958,6 +958,7 @@ _outAggref(StringInfo str, const Aggref *node)
WRITE_NODE_FIELD(args);
WRITE_NODE_FIELD(aggorder);
WRITE_NODE_FIELD(aggdistinct);
+ WRITE_NODE_FIELD(agg_filter);
WRITE_BOOL_FIELD(aggstar);
WRITE_UINT_FIELD(agglevelsup);
WRITE_LOCATION_FIELD(location);
@@ -973,6 +974,7 @@ _outWindowFunc(StringInfo str, const WindowFunc *node)
WRITE_OID_FIELD(wincollid);
WRITE_OID_FIELD(inputcollid);
WRITE_NODE_FIELD(args);
+ WRITE_NODE_FIELD(agg_filter);
WRITE_UINT_FIELD(winref);
WRITE_BOOL_FIELD(winstar);
WRITE_BOOL_FIELD(winagg);
@@ -2083,6 +2085,7 @@ _outFuncCall(StringInfo str, const FuncCall *node)
WRITE_BOOL_FIELD(agg_star);
WRITE_BOOL_FIELD(agg_distinct);
WRITE_BOOL_FIELD(func_variadic);
+ WRITE_NODE_FIELD(agg_filter);
WRITE_NODE_FIELD(over);
WRITE_LOCATION_FIELD(location);
}
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 3a16e9d..c9824b2 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -479,6 +479,7 @@ _readAggref(void)
READ_NODE_FIELD(args);
READ_NODE_FIELD(aggorder);
READ_NODE_FIELD(aggdistinct);
+ READ_NODE_FIELD(agg_filter);
READ_BOOL_FIELD(aggstar);
READ_UINT_FIELD(agglevelsup);
READ_LOCATION_FIELD(location);
@@ -499,6 +500,7 @@ _readWindowFunc(void)
READ_OID_FIELD(wincollid);
READ_OID_FIELD(inputcollid);
READ_NODE_FIELD(args);
+ READ_NODE_FIELD(agg_filter);
READ_UINT_FIELD(winref);
READ_BOOL_FIELD(winstar);
READ_BOOL_FIELD(winagg);
diff --git a/src/backend/optimizer/plan/planagg.c
b/src/backend/optimizer/plan/planagg.c
index 090ae0b..627eb4d 100644
--- a/src/backend/optimizer/plan/planagg.c
+++ b/src/backend/optimizer/plan/planagg.c
@@ -314,7 +314,7 @@ find_minmax_aggs_walker(Node *node, List **context)
ListCell *l;
Assert(aggref->agglevelsup == 0);
- if (list_length(aggref->args) != 1 || aggref->aggorder != NIL)
+ if (list_length(aggref->args) != 1 || aggref->aggorder != NIL
|| aggref->agg_filter != NULL)
return true; /* it couldn't be MIN/MAX */
/* note: we do not care if DISTINCT is mentioned ... */
curTarget = (TargetEntry *) linitial(aggref->args);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0fc5b13..d0961a8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -492,6 +492,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
opt_frame_clause frame_extent frame_bound
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
+%type <node> filter_clause
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
@@ -538,7 +539,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN
EXTENSION EXTERNAL EXTRACT
- FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
+ FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN
FORWARD
FREEZE FROM FULL FUNCTION FUNCTIONS
GLOBAL GRANT GRANTED GREATEST GROUP_P
@@ -11104,6 +11105,7 @@ func_application: func_name '(' ')'
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
+ n->agg_filter = NULL;
n->over = NULL;
n->location = @1;
$$ = (Node *)n;
@@ -11117,6 +11119,7 @@ func_application: func_name '(' ')'
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
+ n->agg_filter = NULL;
n->over = NULL;
n->location = @1;
$$ = (Node *)n;
@@ -11130,6 +11133,7 @@ func_application: func_name '(' ')'
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = TRUE;
+ n->agg_filter = NULL;
n->over = NULL;
n->location = @1;
$$ = (Node *)n;
@@ -11143,6 +11147,7 @@ func_application: func_name '(' ')'
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = TRUE;
+ n->agg_filter = NULL;
n->over = NULL;
n->location = @1;
$$ = (Node *)n;
@@ -11156,6 +11161,7 @@ func_application: func_name '(' ')'
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
+ n->agg_filter = NULL;
n->over = NULL;
n->location = @1;
$$ = (Node *)n;
@@ -11173,6 +11179,7 @@ func_application: func_name '(' ')'
* for that in FuncCall at the moment.
*/
n->func_variadic = FALSE;
+ n->agg_filter = NULL;
n->over = NULL;
n->location = @1;
$$ = (Node *)n;
@@ -11186,6 +11193,7 @@ func_application: func_name '(' ')'
n->agg_star = FALSE;
n->agg_distinct = TRUE;
n->func_variadic = FALSE;
+ n->agg_filter = NULL;
n->over = NULL;
n->location = @1;
$$ = (Node *)n;
@@ -11209,6 +11217,7 @@ func_application: func_name '(' ')'
n->agg_star = TRUE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
+ n->agg_filter = NULL;
n->over = NULL;
n->location = @1;
$$ = (Node *)n;
@@ -11225,10 +11234,11 @@ func_application: func_name '(' ')'
* (Note that many of the special SQL functions wouldn't actually make any
* sense as functional index entries, but we ignore that consideration here.)
*/
-func_expr: func_application over_clause
+func_expr: func_application filter_clause over_clause
{
FuncCall *n = (FuncCall*)$1;
- n->over = $2;
+ n->agg_filter = $2;
+ n->over = $3;
$$ = (Node*)n;
}
| func_expr_common_subexpr
@@ -11797,6 +11807,11 @@ window_definition:
}
;
+filter_clause:
+ FILTER '(' WHERE a_expr ')' { $$ = $4; }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
over_clause: OVER window_specification
{ $$ = $2; }
| OVER ColId
@@ -12771,6 +12786,7 @@ unreserved_keyword:
| EXTENSION
| EXTERNAL
| FAMILY
+ | FILTER
| FIRST_P
| FOLLOWING
| FORCE
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 7380618..e506797 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -44,7 +44,7 @@ typedef struct
int sublevels_up;
} check_ungrouped_columns_context;
-static int check_agg_arguments(ParseState *pstate, List *args);
+static int check_agg_arguments(ParseState *pstate, List *args, Expr
*filter);
static bool check_agg_arguments_walker(Node *node,
check_agg_arguments_context
*context);
static void check_ungrouped_columns(Node *node, ParseState *pstate, Query *qry,
@@ -160,7 +160,7 @@ transformAggregateCall(ParseState *pstate, Aggref *agg,
* Check the arguments to compute the aggregate's level and detect
* improper nesting.
*/
- min_varlevel = check_agg_arguments(pstate, agg->args);
+ min_varlevel = check_agg_arguments(pstate, agg->args, agg->agg_filter);
agg->agglevelsup = min_varlevel;
/* Mark the correct pstate level as having aggregates */
@@ -207,6 +207,9 @@ transformAggregateCall(ParseState *pstate, Aggref *agg,
case EXPR_KIND_HAVING:
/* okay */
break;
+ case EXPR_KIND_FILTER:
+ errkind = true;
+ break;
case EXPR_KIND_WINDOW_PARTITION:
/* okay */
break;
@@ -309,7 +312,7 @@ transformAggregateCall(ParseState *pstate, Aggref *agg,
* which we can't know until we finish scanning the arguments.
*/
static int
-check_agg_arguments(ParseState *pstate, List *args)
+check_agg_arguments(ParseState *pstate, List *args, Expr *filter)
{
int agglevel;
check_agg_arguments_context context;
@@ -323,6 +326,10 @@ check_agg_arguments(ParseState *pstate, List *args)
check_agg_arguments_walker,
(void *)
&context);
+ (void) expression_tree_walker((Node *) filter,
+
check_agg_arguments_walker,
+ (void *)
&context);
+
/*
* If we found no vars nor aggs at all, it's a level-zero aggregate;
* otherwise, its level is the minimum of vars or aggs.
@@ -481,6 +488,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc
*wfunc,
case EXPR_KIND_HAVING:
errkind = true;
break;
+ case EXPR_KIND_FILTER:
+ errkind = true;
+ break;
case EXPR_KIND_WINDOW_PARTITION:
case EXPR_KIND_WINDOW_ORDER:
case EXPR_KIND_WINDOW_FRAME_RANGE:
diff --git a/src/backend/parser/parse_collate.c
b/src/backend/parser/parse_collate.c
index 80f6ac7..b84f2bd 100644
--- a/src/backend/parser/parse_collate.c
+++ b/src/backend/parser/parse_collate.c
@@ -575,6 +575,10 @@ assign_collations_walker(Node *node,
assign_collations_context *context)
* the case above for
T_TargetEntry will apply
* appropriate checks
to agg ORDER BY items.
*
+ * Likewise, we assign
collations for the (bool)
+ * expression in
agg_filter, independently of
+ * any other args.
+ *
* We need not recurse
into the aggorder or
* aggdistinct lists,
because those contain only
* SortGroupClause
nodes which we need not
@@ -595,6 +599,22 @@ assign_collations_walker(Node *node,
assign_collations_context *context)
(void)
assign_collations_walker((Node *) tle,
&loccontext);
}
+
+
assign_expr_collations(context->pstate, (Node *) aggref->agg_filter);
+ }
+ break;
+ case T_WindowFunc:
+ {
+ /*
+ * WindowFunc requires
special processing only for
+ * its agg_filter
clause, as for aggregates.
+ */
+ WindowFunc *wfunc =
(WindowFunc *) node;
+
+ (void)
assign_collations_walker((Node *) wfunc->args,
+
&loccontext);
+
+
assign_expr_collations(context->pstate, (Node *) wfunc->agg_filter);
}
break;
case T_CaseExpr:
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 06f6512..2272965 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -22,6 +22,7 @@
#include "nodes/nodeFuncs.h"
#include "optimizer/var.h"
#include "parser/analyze.h"
+#include "parser/parse_clause.h"
#include "parser/parse_coerce.h"
#include "parser/parse_collate.h"
#include "parser/parse_expr.h"
@@ -463,7 +464,7 @@ transformIndirection(ParseState *pstate, Node *basenode,
List *indirection)
list_make1(n),
list_make1(result),
NIL, false, false, false,
-
NULL, true, location);
+
NULL, NULL, true, location);
if (newresult == NULL)
unknown_attribute(pstate, result, strVal(n),
location);
result = newresult;
@@ -631,7 +632,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(colname)),
list_make1(node),
NIL, false, false, false,
-
NULL, true, cref->location);
+
NULL, NULL, true, cref->location);
}
break;
}
@@ -676,7 +677,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(colname)),
list_make1(node),
NIL, false, false, false,
-
NULL, true, cref->location);
+
NULL, NULL, true, cref->location);
}
break;
}
@@ -734,7 +735,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(colname)),
list_make1(node),
NIL, false, false, false,
-
NULL, true, cref->location);
+
NULL, NULL, true, cref->location);
}
break;
}
@@ -1241,6 +1242,7 @@ transformFuncCall(ParseState *pstate, FuncCall *fn)
{
List *targs;
ListCell *args;
+ Expr *tagg_filter;
/* Transform the list of arguments ... */
targs = NIL;
@@ -1250,6 +1252,12 @@ transformFuncCall(ParseState *pstate, FuncCall *fn)
(Node *) lfirst(args)));
}
+ /* Transform the aggregate filter using transformWhereClause, to
+ * which FILTER is virually identical... */
+ tagg_filter = NULL;
+ if (fn->agg_filter != NULL)
+ tagg_filter = (Expr *)transformWhereClause(pstate, (Node
*)fn->agg_filter, EXPR_KIND_FILTER, "FILTER");
+
/* ... and hand off to ParseFuncOrColumn */
return ParseFuncOrColumn(pstate,
fn->funcname,
@@ -1258,6 +1266,7 @@ transformFuncCall(ParseState *pstate, FuncCall *fn)
fn->agg_star,
fn->agg_distinct,
fn->func_variadic,
+ tagg_filter,
fn->over,
false,
fn->location);
@@ -1430,6 +1439,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_FROM_FUNCTION:
case EXPR_KIND_WHERE:
case EXPR_KIND_HAVING:
+ case EXPR_KIND_FILTER:
case EXPR_KIND_WINDOW_PARTITION:
case EXPR_KIND_WINDOW_ORDER:
case EXPR_KIND_WINDOW_FRAME_RANGE:
@@ -2579,6 +2589,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "WHERE";
case EXPR_KIND_HAVING:
return "HAVING";
+ case EXPR_KIND_FILTER:
+ return "FILTER";
case EXPR_KIND_WINDOW_PARTITION:
return "window PARTITION BY";
case EXPR_KIND_WINDOW_ORDER:
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index ae7d195..75c740e 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -63,7 +63,7 @@ Node *
ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
List *agg_order, bool agg_star, bool
agg_distinct,
bool func_variadic,
- WindowDef *over, bool is_column, int location)
+ Expr *agg_filter, WindowDef *over, bool
is_column, int location)
{
Oid rettype;
Oid funcid;
@@ -175,7 +175,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List
*fargs,
* wasn't any aggregate or variadic decoration, nor an argument name.
*/
if (nargs == 1 && agg_order == NIL && !agg_star && !agg_distinct &&
- over == NULL && !func_variadic && argnames == NIL &&
+ agg_filter == NULL && over == NULL && !func_variadic &&
argnames == NIL &&
list_length(funcname) == 1)
{
Oid argtype = actual_arg_types[0];
@@ -251,6 +251,12 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List
*fargs,
errmsg("ORDER BY specified, but %s is not an aggregate
function",
NameListToString(funcname)),
parser_errposition(pstate, location)));
+ if (agg_filter)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("FILTER specified, but %s is
not an aggregate function",
+
NameListToString(funcname)),
+ parser_errposition(pstate, location)));
if (over)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -402,6 +408,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List
*fargs,
/* aggcollid and inputcollid will be set by parse_collate.c */
/* args, aggorder, aggdistinct will be set by
transformAggregateCall */
aggref->aggstar = agg_star;
+ /* filter */
+ aggref->agg_filter = agg_filter;
/* agglevelsup will be set by transformAggregateCall */
aggref->location = location;
@@ -460,6 +468,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List
*fargs,
/* winref will be set by transformWindowFuncCall */
wfunc->winstar = agg_star;
wfunc->winagg = (fdresult == FUNCDETAIL_AGGREGATE);
+ wfunc->agg_filter = agg_filter;
wfunc->location = location;
/*
@@ -483,6 +492,16 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List
*fargs,
parser_errposition(pstate, location)));
/*
+ * Reject window functions which are not aggregates in the
+ * case of FILTER.
+ */
+ if (!wfunc->winagg && agg_filter)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("FILTER is not implemented in
non-aggregate window functions"),
+ parser_errposition(pstate, location)));
+
+ /*
* ordered aggs not allowed in windows yet
*/
if (agg_order != NIL)
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index a1ed781..bd3050a 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -7424,7 +7424,15 @@ get_agg_expr(Aggref *aggref, deparse_context *context)
appendStringInfoString(buf, " ORDER BY ");
get_rule_orderby(aggref->aggorder, aggref->args, false,
context);
}
+
+ if (aggref->agg_filter != NULL)
+ {
+ appendStringInfoString(buf, ") FILTER (WHERE ");
+ get_rule_expr((Node *)aggref->agg_filter, context, false);
+ }
+
appendStringInfoChar(buf, ')');
+
}
/*
@@ -7461,6 +7469,13 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context
*context)
appendStringInfoChar(buf, '*');
else
get_rule_expr((Node *) wfunc->args, context, true);
+
+ if (wfunc->agg_filter != NULL)
+ {
+ appendStringInfoString(buf, ") FILTER (WHERE ");
+ get_rule_expr((Node *)wfunc->agg_filter, context, false);
+ }
+
appendStringInfoString(buf, ") OVER ");
foreach(l, context->windowClause)
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 4f77016..a3e6b05 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -584,6 +584,7 @@ typedef struct AggrefExprState
{
ExprState xprstate;
List *args; /* states of argument
expressions */
+ ExprState *agg_filter; /* FILTER expression */
int aggno; /* ID number for agg
within its plan node */
} AggrefExprState;
@@ -595,6 +596,7 @@ typedef struct WindowFuncExprState
{
ExprState xprstate;
List *args; /* states of argument
expressions */
+ ExprState *agg_filter; /* FILTER expression */
int wfuncno; /* ID number for wfunc
within its plan node */
} WindowFuncExprState;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 9453e1d..4519602 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -295,6 +295,7 @@ typedef struct FuncCall
bool agg_star; /* argument was really '*' */
bool agg_distinct; /* arguments were labeled DISTINCT */
bool func_variadic; /* last argument was labeled VARIADIC */
+ Node *agg_filter; /* FILTER clause, if any */
struct WindowDef *over; /* OVER clause, if any */
int location; /* token location, or
-1 if unknown */
} FuncCall;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 75b716a..9f7111e 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -247,6 +247,7 @@ typedef struct Aggref
List *args; /* arguments and sort
expressions */
List *aggorder; /* ORDER BY (list of SortGroupClause) */
List *aggdistinct; /* DISTINCT (list of SortGroupClause) */
+ Expr *agg_filter; /* FILTER expression */
bool aggstar; /* TRUE if argument list was
really '*' */
Index agglevelsup; /* > 0 if agg belongs to outer query */
int location; /* token location, or
-1 if unknown */
@@ -263,6 +264,7 @@ typedef struct WindowFunc
Oid wincollid; /* OID of collation of
result */
Oid inputcollid; /* OID of collation that
function should use */
List *args; /* arguments to the window
function */
+ Expr *agg_filter; /* FILTER expression */
Index winref; /* index of associated
WindowClause */
bool winstar; /* TRUE if argument list was
really '*' */
bool winagg; /* is function a simple
aggregate? */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index b3d72a9..287f78e 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -155,6 +155,7 @@ PG_KEYWORD("extract", EXTRACT, COL_NAME_KEYWORD)
PG_KEYWORD("false", FALSE_P, RESERVED_KEYWORD)
PG_KEYWORD("family", FAMILY, UNRESERVED_KEYWORD)
PG_KEYWORD("fetch", FETCH, RESERVED_KEYWORD)
+PG_KEYWORD("filter", FILTER, UNRESERVED_KEYWORD)
PG_KEYWORD("first", FIRST_P, UNRESERVED_KEYWORD)
PG_KEYWORD("float", FLOAT_P, COL_NAME_KEYWORD)
PG_KEYWORD("following", FOLLOWING, UNRESERVED_KEYWORD)
diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h
index 6e09dc4..13efb57 100644
--- a/src/include/parser/parse_func.h
+++ b/src/include/parser/parse_func.h
@@ -46,7 +46,7 @@ extern Node *ParseFuncOrColumn(ParseState *pstate,
List *funcname, List *fargs,
List *agg_order, bool agg_star, bool
agg_distinct,
bool func_variadic,
- WindowDef *over, bool is_column, int
location);
+ Expr *agg_filter, WindowDef *over, bool
is_column, int location);
extern FuncDetailCode func_get_detail(List *funcname,
List *fargs, List *fargnames,
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 49ca764..bea3b07 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -39,6 +39,7 @@ typedef enum ParseExprKind
EXPR_KIND_FROM_FUNCTION, /* function in FROM clause */
EXPR_KIND_WHERE, /* WHERE */
EXPR_KIND_HAVING, /* HAVING */
+ EXPR_KIND_FILTER, /* FILTER */
EXPR_KIND_WINDOW_PARTITION, /* window definition PARTITION BY */
EXPR_KIND_WINDOW_ORDER, /* window definition ORDER BY */
EXPR_KIND_WINDOW_FRAME_RANGE, /* window frame clause with
RANGE */
diff --git a/src/test/regress/expected/aggregates.out
b/src/test/regress/expected/aggregates.out
index d379c0d..9359811 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1154,3 +1154,69 @@ select string_agg(v, decode('ee', 'hex')) from
bytea_test_table;
(1 row)
drop table bytea_test_table;
+-- FILTER tests
+select min(unique1) filter (where unique1 > 100) from tenk1;
+ min
+-----
+ 101
+(1 row)
+
+select ten, sum(distinct four) filter (where four::text ~ '123') from onek a
+group by ten;
+ ten | sum
+-----+-----
+ 0 |
+ 1 |
+ 2 |
+ 3 |
+ 4 |
+ 5 |
+ 6 |
+ 7 |
+ 8 |
+ 9 |
+(10 rows)
+
+select ten, sum(distinct four) filter (where four > 10) from onek a
+group by ten
+having exists (select 1 from onek b where sum(distinct a.four) = b.four);
+ ten | sum
+-----+-----
+ 0 |
+ 2 |
+ 4 |
+ 6 |
+ 8 |
+(5 rows)
+
+select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0') from
(values ('a', 'b')) AS v(foo,bar);
+ max
+-----
+ a
+(1 row)
+
+-- outer-level aggregates
+select
+ (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1))
filter (where o.unique1 < 10))
+from tenk1 o;
+ max
+------
+ 9998
+(1 row)
+
+-- non-standard-conforming FILTER clause containing subquery
+select sum(unique1) FILTER (WHERE unique1 IN (SELECT unique1 FROM onek where
unique1 < 100)) FROM tenk1;
+ sum
+------
+ 4950
+(1 row)
+
+-- exercise lots of aggregate parts with FILTER
+select aggfns(distinct a,b,c order by a,c using ~<~,b) filter (where a > 1)
+ from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+ generate_series(1,2) i;
+ aggfns
+---------------------------
+ {"(2,2,bar)","(3,1,baz)"}
+(1 row)
+
diff --git a/src/test/regress/expected/window.out
b/src/test/regress/expected/window.out
index ecc1c2c..7b31d13 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -1020,5 +1020,18 @@ SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM
tenk1;
ERROR: argument of ntile must be greater than zero
SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
ERROR: argument of nth_value must be greater than zero
+-- filter
+SELECT sum(salary), row_number() OVER (ORDER BY depname), sum(
+ sum(salary) FILTER (WHERE enroll_date > '2007-01-01')
+) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS
"filtered_sum",
+ depname
+FROM empsalary GROUP BY depname;
+ sum | row_number | filtered_sum | depname
+-------+------------+--------------+-----------
+ 14600 | 3 | | sales
+ 7400 | 2 | 3500 | personnel
+ 25100 | 1 | 22600 | develop
+(3 rows)
+
-- cleanup
DROP TABLE empsalary;
diff --git a/src/test/regress/sql/aggregates.sql
b/src/test/regress/sql/aggregates.sql
index 38d4757..da0bd65 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -442,3 +442,31 @@ select string_agg(v, NULL) from bytea_test_table;
select string_agg(v, decode('ee', 'hex')) from bytea_test_table;
drop table bytea_test_table;
+
+-- FILTER tests
+
+select min(unique1) filter (where unique1 > 100) from tenk1;
+
+select ten, sum(distinct four) filter (where four::text ~ '123') from onek a
+group by ten;
+
+select ten, sum(distinct four) filter (where four > 10) from onek a
+group by ten
+having exists (select 1 from onek b where sum(distinct a.four) = b.four);
+
+select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0') from
(values ('a', 'b')) AS v(foo,bar);
+
+-- outer-level aggregates
+select
+ (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1))
filter (where o.unique1 < 10))
+from tenk1 o;
+
+-- non-standard-conforming FILTER clause containing subquery
+
+select sum(unique1) FILTER (WHERE unique1 IN (SELECT unique1 FROM onek where
unique1 < 100)) FROM tenk1;
+
+-- exercise lots of aggregate parts with FILTER
+
+select aggfns(distinct a,b,c order by a,c using ~<~,b) filter (where a > 1)
+ from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+ generate_series(1,2) i;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 769be0f..6ee3696 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -264,5 +264,13 @@ SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
+-- filter
+
+SELECT sum(salary), row_number() OVER (ORDER BY depname), sum(
+ sum(salary) FILTER (WHERE enroll_date > '2007-01-01')
+) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS
"filtered_sum",
+ depname
+FROM empsalary GROUP BY depname;
+
-- cleanup
DROP TABLE empsalary;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
