On Tue, Feb 26, 2013 at 01:09:30PM -0800, David Fetter wrote:
> On Wed, Feb 13, 2013 at 06:45:31AM -0800, David Fetter wrote:
> > On Sat, Feb 09, 2013 at 11:59:22PM -0800, David Fetter wrote:
> > > Folks,
> > >
> > > Per suggestions and lots of help from Andrew Gierth, please find
> > > attached a patch to clean up the call sites for FuncCall nodes, which
> > > I'd like to expand centrally rather than in each of the 37 (or 38, but
> > > I only redid 37) places where it's called. The remaining one is in
> > > src/backend/nodes/copyfuncs.c, which has to be modified for any
> > > changes in the that struct anyhow.
> > >
> > > The immediate purpose is two-fold: to reduce some redundancies, which
> > > I believe is worth doing in and of itself, and to prepare for adding
> > > FILTER on aggregates from the spec, and possibly other things in
> > > the <aggregate function> part of the spec.
> > >
> > > Cheers,
> > > David.
> >
> > Folks,
> >
> > Please find attached two versions of a patch which provides optional
> > FILTER clause for aggregates (T612, "Advanced OLAP operations").
> >
> > The first is intended to be applied on top of the previous patch, the
> > second without it. The first is, I believe, clearer in what it's
> > doing. Rather than simply mechanically visiting every place a
> > function call might be constructed, it visits a central one to change
> > the default, then goes only to the places where it's relevant.
> >
> > The patches are both early WIP as they contain no docs or regression
> > tests yet.
>
> Docs and regression tests added, makeFuncArgs approached dropped for
> now, will re-visit later.
Regression tests added to reflect bug fixes in COLLATE.
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
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***************
*** 595,604 **** GROUP BY <replaceable
class="parameter">expression</replaceable> [, ...]
</para>
<para>
! 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 <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
--- 595,607 ----
</para>
<para>
! 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
*** a/doc/src/sgml/syntax.sgml
--- b/doc/src/sgml/syntax.sgml
***************
*** 1562,1585 **** 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> ( * )
</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.
</para>
<para>
! The first form of aggregate expression invokes the aggregate
! once for each input row.
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
--- 1562,1587 ----
syntax of an aggregate expression is one of the following:
<synopsis>
! <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, <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, 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,1612 **** sqrt(2)
--- 1609,1629 ----
</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,1718 **** 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>
</synopsis>
where <replaceable class="parameter">window_definition</replaceable>
has the syntax
--- 1726,1735 ----
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>) [ 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,1851 **** 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.
</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
function argument list.
</para>
--- 1853,1870 ----
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. 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
function argument list.
</para>
*** a/src/backend/executor/execQual.c
--- b/src/backend/executor/execQual.c
***************
*** 4402,4407 **** ExecInitExpr(Expr *node, PlanState *parent)
--- 4402,4408 ----
astate->args = (List *)
ExecInitExpr((Expr *) aggref->args,
parent);
+ astate->agg_filter =
ExecInitExpr(aggref->agg_filter, parent);
/*
* Complain if the aggregate's
arguments contain any
***************
*** 4440,4445 **** ExecInitExpr(Expr *node, PlanState *parent)
--- 4441,4447 ----
wfstate->args = (List *)
ExecInitExpr((Expr *) wfunc->args,
parent);
+ wfstate->agg_filter =
ExecInitExpr(wfunc->agg_filter, parent);
/*
* Complain if the windowfunc's
arguments contain any
*** a/src/backend/executor/functions.c
--- b/src/backend/executor/functions.c
***************
*** 381,387 **** 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);
}
return param;
--- 381,387 ----
list_make1(subfield),
list_make1(param),
NIL, false,
false, false,
! NULL, NULL,
true, cref->location);
}
return param;
*** a/src/backend/executor/nodeAgg.c
--- b/src/backend/executor/nodeAgg.c
***************
*** 488,493 **** advance_aggregates(AggState *aggstate, AggStatePerGroup
pergroup)
--- 488,505 ----
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);
*** a/src/backend/executor/nodeWindowAgg.c
--- b/src/backend/executor/nodeWindowAgg.c
***************
*** 227,235 **** advance_windowaggregate(WindowAggState *winstate,
--- 227,248 ----
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)
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 1137,1142 **** _copyAggref(const Aggref *from)
--- 1137,1143 ----
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,1162 **** _copyWindowFunc(const WindowFunc *from)
--- 1158,1164 ----
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,2160 **** _copyFuncCall(const FuncCall *from)
--- 2157,2163 ----
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);
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 196,201 **** _equalAggref(const Aggref *a, const Aggref *b)
--- 196,202 ----
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,216 **** _equalWindowFunc(const WindowFunc *a, const WindowFunc *b)
--- 212,218 ----
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);
***************
*** 2009,2014 **** _equalFuncCall(const FuncCall *a, const FuncCall *b)
--- 2011,2017 ----
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);
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 1570,1575 **** expression_tree_walker(Node *node,
--- 1570,1578 ----
if (expression_tree_walker((Node *)
expr->aggdistinct,
walker, context))
return true;
+ if (expression_tree_walker((Node *)
expr->agg_filter,
+
walker, context))
+ return true;
}
break;
case T_WindowFunc:
***************
*** 1580,1585 **** expression_tree_walker(Node *node,
--- 1583,1591 ----
if (expression_tree_walker((Node *) expr->args,
walker, context))
return true;
+ if (expression_tree_walker((Node *)
expr->agg_filter,
+
walker, context))
+ return true;
}
break;
case T_ArrayRef:
***************
*** 2079,2084 **** expression_tree_mutator(Node *node,
--- 2085,2091 ----
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,2094 **** expression_tree_mutator(Node *node,
--- 2096,2102 ----
FLATCOPY(newnode, wfunc, WindowFunc);
MUTATE(newnode->args, wfunc->args, List *);
+ MUTATE(newnode->agg_filter, wfunc->agg_filter,
Expr *);
return (Node *) newnode;
}
break;
***************
*** 2951,2956 **** raw_expression_tree_walker(Node *node,
--- 2959,2966 ----
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 */
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 958,963 **** _outAggref(StringInfo str, const Aggref *node)
--- 958,964 ----
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,978 **** _outWindowFunc(StringInfo str, const WindowFunc *node)
--- 974,980 ----
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,2088 **** _outFuncCall(StringInfo str, const FuncCall *node)
--- 2085,2091 ----
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);
}
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 479,484 **** _readAggref(void)
--- 479,485 ----
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,504 **** _readWindowFunc(void)
--- 500,506 ----
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);
*** a/src/backend/optimizer/plan/planagg.c
--- b/src/backend/optimizer/plan/planagg.c
***************
*** 313,319 **** find_minmax_aggs_walker(Node *node, List **context)
ListCell *l;
Assert(aggref->agglevelsup == 0);
! if (list_length(aggref->args) != 1 || aggref->aggorder != NIL)
return true; /* it couldn't be MIN/MAX */
/* note: we do not care if DISTINCT is mentioned ... */
curTarget = (TargetEntry *) linitial(aggref->args);
--- 313,319 ----
ListCell *l;
Assert(aggref->agglevelsup == 0);
! 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);
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 489,494 **** static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
--- 489,495 ----
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.
***************
*** 535,541 **** 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
FREEZE FROM FULL FUNCTION FUNCTIONS
GLOBAL GRANT GRANTED GREATEST GROUP_P
--- 536,542 ----
EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN
EXTENSION EXTERNAL EXTRACT
! 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
***************
*** 11087,11093 **** c_expr: columnref
{ $$ = $1; }
* (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_name '(' ')' over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 11088,11094 ----
* (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_name '(' ')' filter_clause over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
***************
*** 11096,11119 **** func_expr: func_name '(' ')' over_clause
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
! n->over = $4;
! n->location = @1;
! $$ = (Node *)n;
! }
! | func_name '(' func_arg_list ')' over_clause
! {
! FuncCall *n = makeNode(FuncCall);
! n->funcname = $1;
! n->args = $3;
! n->agg_order = NIL;
! n->agg_star = FALSE;
! n->agg_distinct = FALSE;
! n->func_variadic = FALSE;
n->over = $5;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' VARIADIC func_arg_expr ')' over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 11097,11122 ----
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
! n->agg_filter = $4;
n->over = $5;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' func_arg_list ')' filter_clause
over_clause
! {
! FuncCall *n = makeNode(FuncCall);
! n->funcname = $1;
! n->args = $3;
! n->agg_order = NIL;
! n->agg_star = FALSE;
! n->agg_distinct = FALSE;
! n->func_variadic = FALSE;
! n->agg_filter = $5;
! n->over = $6;
! n->location = @1;
! $$ = (Node *)n;
! }
! | func_name '(' VARIADIC func_arg_expr ')'
filter_clause over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
***************
*** 11122,11132 **** func_expr: func_name '(' ')' over_clause
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = TRUE;
! n->over = $6;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' func_arg_list ',' VARIADIC
func_arg_expr ')' over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 11125,11136 ----
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = TRUE;
! n->agg_filter = $6;
! n->over = $7;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' func_arg_list ',' VARIADIC
func_arg_expr ')' filter_clause over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
***************
*** 11135,11145 **** func_expr: func_name '(' ')' over_clause
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = TRUE;
! n->over = $8;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' func_arg_list sort_clause ')'
over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 11139,11150 ----
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = TRUE;
! n->agg_filter = $8;
! n->over = $9;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' func_arg_list sort_clause ')'
filter_clause over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
***************
*** 11148,11158 **** func_expr: func_name '(' ')' over_clause
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
! n->over = $6;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' ALL func_arg_list opt_sort_clause ')'
over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 11153,11164 ----
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
! n->agg_filter = $6;
! n->over = $7;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' ALL func_arg_list opt_sort_clause ')'
filter_clause over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
***************
*** 11165,11175 **** func_expr: func_name '(' ')' over_clause
* for that in FuncCall at the moment.
*/
n->func_variadic = FALSE;
! n->over = $7;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' DISTINCT func_arg_list opt_sort_clause
')' over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 11171,11182 ----
* for that in FuncCall at the moment.
*/
n->func_variadic = FALSE;
! n->agg_filter = $7;
! n->over = $8;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' DISTINCT func_arg_list opt_sort_clause
')' filter_clause over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
***************
*** 11178,11188 **** func_expr: func_name '(' ')' over_clause
n->agg_star = FALSE;
n->agg_distinct = TRUE;
n->func_variadic = FALSE;
! n->over = $7;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' '*' ')' over_clause
{
/*
* We consider AGGREGATE(*) to invoke a
parameterless
--- 11185,11196 ----
n->agg_star = FALSE;
n->agg_distinct = TRUE;
n->func_variadic = FALSE;
! n->agg_filter = $7;
! n->over = $8;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' '*' ')' filter_clause over_clause
{
/*
* We consider AGGREGATE(*) to invoke a
parameterless
***************
*** 11201,11207 **** func_expr: func_name '(' ')' over_clause
n->agg_star = TRUE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
! n->over = $5;
n->location = @1;
$$ = (Node *)n;
}
--- 11209,11216 ----
n->agg_star = TRUE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
! n->agg_filter = $5;
! n->over = $6;
n->location = @1;
$$ = (Node *)n;
}
***************
*** 11735,11741 **** xmlexists_argument:
window_clause:
WINDOW window_definition_list { $$ =
$2; }
| /*EMPTY*/
{ $$ = NIL; }
! ;
window_definition_list:
window_definition
{ $$ = list_make1($1); }
--- 11744,11750 ----
window_clause:
WINDOW window_definition_list { $$ =
$2; }
| /*EMPTY*/
{ $$ = NIL; }
! ;
window_definition_list:
window_definition
{ $$ = list_make1($1); }
***************
*** 11752,11757 **** window_definition:
--- 11761,11771 ----
}
;
+ filter_clause:
+ FILTER '(' WHERE a_expr ')' { $$ = $4; }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
over_clause: OVER window_specification
{ $$ = $2; }
| OVER ColId
***************
*** 12972,12977 **** type_func_name_keyword:
--- 12986,12992 ----
| CONCURRENTLY
| CROSS
| CURRENT_SCHEMA
+ | FILTER
| FREEZE
| FULL
| ILIKE
*** a/src/backend/parser/parse_agg.c
--- b/src/backend/parser/parse_agg.c
***************
*** 44,50 **** typedef struct
int sublevels_up;
} check_ungrouped_columns_context;
! static int check_agg_arguments(ParseState *pstate, List *args);
static bool check_agg_arguments_walker(Node *node,
check_agg_arguments_context
*context);
static void check_ungrouped_columns(Node *node, ParseState *pstate, Query
*qry,
--- 44,50 ----
int sublevels_up;
} check_ungrouped_columns_context;
! 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,166 **** 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);
agg->agglevelsup = min_varlevel;
/* Mark the correct pstate level as having aggregates */
--- 160,166 ----
* Check the arguments to compute the aggregate's level and detect
* improper nesting.
*/
! min_varlevel = check_agg_arguments(pstate, agg->args, agg->agg_filter);
agg->agglevelsup = min_varlevel;
/* Mark the correct pstate level as having aggregates */
***************
*** 207,212 **** transformAggregateCall(ParseState *pstate, Aggref *agg,
--- 207,215 ----
case EXPR_KIND_HAVING:
/* okay */
break;
+ case EXPR_KIND_FILTER:
+ errkind = true;
+ break;
case EXPR_KIND_WINDOW_PARTITION:
/* okay */
break;
***************
*** 309,315 **** 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)
{
int agglevel;
check_agg_arguments_context context;
--- 312,318 ----
* which we can't know until we finish scanning the arguments.
*/
static int
! check_agg_arguments(ParseState *pstate, List *args, Expr *filter)
{
int agglevel;
check_agg_arguments_context context;
***************
*** 323,328 **** check_agg_arguments(ParseState *pstate, List *args)
--- 326,335 ----
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,486 **** transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
--- 488,496 ----
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:
*** a/src/backend/parser/parse_collate.c
--- b/src/backend/parser/parse_collate.c
***************
*** 575,580 **** assign_collations_walker(Node *node, assign_collations_context
*context)
--- 575,584 ----
* 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,600 **** assign_collations_walker(Node *node, assign_collations_context
*context)
--- 599,620 ----
(void)
assign_collations_walker((Node *) tle,
&loccontext);
}
+
+
assign_expr_collations(context->pstate, 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, wfunc->agg_filter);
}
break;
case T_CaseExpr:
*** a/src/backend/parser/parse_expr.c
--- b/src/backend/parser/parse_expr.c
***************
*** 22,27 ****
--- 22,28 ----
#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,469 **** transformIndirection(ParseState *pstate, Node *basenode, List
*indirection)
list_make1(n),
list_make1(result),
NIL, false, false, false,
!
NULL, true, location);
if (newresult == NULL)
unknown_attribute(pstate, result, strVal(n),
location);
result = newresult;
--- 464,470 ----
list_make1(n),
list_make1(result),
NIL, false, false, false,
!
NULL, NULL, true, location);
if (newresult == NULL)
unknown_attribute(pstate, result, strVal(n),
location);
result = newresult;
***************
*** 631,637 **** transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(colname)),
list_make1(node),
NIL, false, false, false,
!
NULL, true, cref->location);
}
break;
}
--- 632,638 ----
list_make1(makeString(colname)),
list_make1(node),
NIL, false, false, false,
!
NULL, NULL, true, cref->location);
}
break;
}
***************
*** 676,682 **** transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(colname)),
list_make1(node),
NIL, false, false, false,
!
NULL, true, cref->location);
}
break;
}
--- 677,683 ----
list_make1(makeString(colname)),
list_make1(node),
NIL, false, false, false,
!
NULL, NULL, true, cref->location);
}
break;
}
***************
*** 734,740 **** transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(colname)),
list_make1(node),
NIL, false, false, false,
!
NULL, true, cref->location);
}
break;
}
--- 735,741 ----
list_make1(makeString(colname)),
list_make1(node),
NIL, false, false, false,
!
NULL, NULL, true, cref->location);
}
break;
}
***************
*** 1241,1246 **** transformFuncCall(ParseState *pstate, FuncCall *fn)
--- 1242,1248 ----
{
List *targs;
ListCell *args;
+ Expr *tagg_filter;
/* Transform the list of arguments ... */
targs = NIL;
***************
*** 1250,1255 **** transformFuncCall(ParseState *pstate, FuncCall *fn)
--- 1252,1263 ----
(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,1263 **** transformFuncCall(ParseState *pstate, FuncCall *fn)
--- 1266,1272 ----
fn->agg_star,
fn->agg_distinct,
fn->func_variadic,
+ tagg_filter,
fn->over,
false,
fn->location);
***************
*** 1430,1435 **** transformSubLink(ParseState *pstate, SubLink *sublink)
--- 1439,1445 ----
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,2584 **** ParseExprKindName(ParseExprKind exprKind)
--- 2589,2596 ----
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:
*** a/src/backend/parser/parse_func.c
--- b/src/backend/parser/parse_func.c
***************
*** 63,69 **** 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)
{
Oid rettype;
Oid funcid;
--- 63,69 ----
ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
List *agg_order, bool agg_star, bool
agg_distinct,
bool func_variadic,
! Expr *agg_filter, WindowDef *over, bool
is_column, int location)
{
Oid rettype;
Oid funcid;
***************
*** 175,181 **** 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 &&
list_length(funcname) == 1)
{
Oid argtype = actual_arg_types[0];
--- 175,181 ----
* wasn't any aggregate or variadic decoration, nor an argument name.
*/
if (nargs == 1 && agg_order == NIL && !agg_star && !agg_distinct &&
! agg_filter == NULL && over == NULL && !func_variadic &&
argnames == NIL &&
list_length(funcname) == 1)
{
Oid argtype = actual_arg_types[0];
***************
*** 251,256 **** ParseFuncOrColumn(ParseState *pstate, List *funcname, List
*fargs,
--- 251,262 ----
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,407 **** ParseFuncOrColumn(ParseState *pstate, List *funcname, List
*fargs,
--- 408,415 ----
/* 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,465 **** ParseFuncOrColumn(ParseState *pstate, List *funcname, List
*fargs,
--- 468,474 ----
/* winref will be set by transformWindowFuncCall */
wfunc->winstar = agg_star;
wfunc->winagg = (fdresult == FUNCDETAIL_AGGREGATE);
+ wfunc->agg_filter = agg_filter;
wfunc->location = location;
/*
***************
*** 483,488 **** ParseFuncOrColumn(ParseState *pstate, List *funcname, List
*fargs,
--- 492,507 ----
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)
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 7419,7425 **** get_agg_expr(Aggref *aggref, deparse_context *context)
--- 7419,7433 ----
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, ')');
+
}
/*
***************
*** 7456,7461 **** get_windowfunc_expr(WindowFunc *wfunc, deparse_context
*context)
--- 7464,7476 ----
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)
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 584,589 **** typedef struct AggrefExprState
--- 584,590 ----
{
ExprState xprstate;
List *args; /* states of argument
expressions */
+ Expr *agg_filter; /* FILTER expression */
int aggno; /* ID number for agg
within its plan node */
} AggrefExprState;
***************
*** 595,600 **** typedef struct WindowFuncExprState
--- 596,602 ----
{
ExprState xprstate;
List *args; /* states of argument
expressions */
+ Expr *agg_filter; /* FILTER expression */
int wfuncno; /* ID number for wfunc
within its plan node */
} WindowFuncExprState;
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 295,300 **** typedef struct FuncCall
--- 295,301 ----
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;
*** a/src/include/nodes/primnodes.h
--- b/src/include/nodes/primnodes.h
***************
*** 247,252 **** typedef struct Aggref
--- 247,253 ----
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,268 **** typedef struct WindowFunc
--- 264,270 ----
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? */
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 155,160 **** PG_KEYWORD("extract", EXTRACT, COL_NAME_KEYWORD)
--- 155,161 ----
PG_KEYWORD("false", FALSE_P, RESERVED_KEYWORD)
PG_KEYWORD("family", FAMILY, UNRESERVED_KEYWORD)
PG_KEYWORD("fetch", FETCH, RESERVED_KEYWORD)
+ PG_KEYWORD("filter", FILTER, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("first", FIRST_P, UNRESERVED_KEYWORD)
PG_KEYWORD("float", FLOAT_P, COL_NAME_KEYWORD)
PG_KEYWORD("following", FOLLOWING, UNRESERVED_KEYWORD)
*** a/src/include/parser/parse_func.h
--- b/src/include/parser/parse_func.h
***************
*** 46,52 **** 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);
extern FuncDetailCode func_get_detail(List *funcname,
List *fargs, List *fargnames,
--- 46,52 ----
List *funcname, List *fargs,
List *agg_order, bool agg_star, bool
agg_distinct,
bool func_variadic,
! Expr *agg_filter, WindowDef *over, bool
is_column, int location);
extern FuncDetailCode func_get_detail(List *funcname,
List *fargs, List *fargnames,
*** a/src/include/parser/parse_node.h
--- b/src/include/parser/parse_node.h
***************
*** 39,44 **** typedef enum ParseExprKind
--- 39,45 ----
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 */
*** a/src/test/regress/expected/aggregates.out
--- b/src/test/regress/expected/aggregates.out
***************
*** 1154,1156 **** select string_agg(v, decode('ee', 'hex')) from
bytea_test_table;
--- 1154,1215 ----
(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)
+
+ -- 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)
+
*** a/src/test/regress/expected/window.out
--- b/src/test/regress/expected/window.out
***************
*** 1020,1024 **** SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
--- 1020,1037 ----
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;
*** a/src/test/regress/sql/aggregates.sql
--- b/src/test/regress/sql/aggregates.sql
***************
*** 442,444 **** select string_agg(v, NULL) from bytea_test_table;
--- 442,468 ----
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;
+
+ -- 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;
*** a/src/test/regress/sql/window.sql
--- b/src/test/regress/sql/window.sql
***************
*** 264,268 **** SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
--- 264,276 ----
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
