*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 10036,10041 **** SELECT count(*) FROM sometable;
--- 10036,10137 ----
+
+ Windowing Functions
+
+
+ Windowing functions provides facilities of
+ windowed table calculation, including windowed aggregate and rank.
+
+ The built-in windowing rank functions are listed in
+ .
+ All of aggregate functions in and
+ can be also used in
+ windowing table.
+
+
+
+ General-Purpose Rank Functions
+
+
+
+
+ Function
+ Return Type
+ Description
+
+
+
+
+
+
+
+ rank
+
+ rank() OVER (ORDER BY expression)
+
+
+ bigint
+
+ number of the row from 1 ordered by expressions, with gaps
+
+
+
+
+
+ dense_rank
+
+ dense_rank() OVER (ORDER BY expression)
+
+
+ bigint
+
+ number of the row from 1 ordered by expressions, without gaps
+
+
+
+
+
+ rownumber()
+
+ rownumber() OVER (ORDER BY expression)
+
+
+ bigint
+
+ number of the row from 1 ordered by expressions, incrementing always
+
+
+
+
+
+ percent_rank()
+
+ percent_rank() OVER (ORDER BY expression)
+
+
+ double precision
+
+ relative rank of the row between 0 and 1 ordered by expressions, with gap
+
+
+
+
+
+ cume_dist()
+
+ cume_dist() OVER (ORDER BY expression)
+
+
+ double precision
+
+ relative rank of the row between 0 and 1 ordered by expressions, without gap
+
+
+
+
+
+
Subquery Expressions
*** a/doc/src/sgml/query.sgml
--- b/doc/src/sgml/query.sgml
***************
*** 805,810 **** SELECT city, max(temp_lo)
--- 805,909 ----
+
+ Windowing Functions
+
+
+ A windowing function is the operation across a set of rows in
+ a windowed table. This may sound similar to aggregate functions,
+ but contrast to that windowing functions don't reduce rows.
+ Additionally, windowing functions can compute different results
+ row by row.
+
+
+
+ Here is an example that shows how to compare each employee's salary
+ with the department average salary.
+
+
+ SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
+
+
+
+ depname | empno | salary | avg
+ -----------+-------+--------+-----------------------
+ develop | 11 | 5200 | 5020.0000000000000000
+ develop | 7 | 4200 | 5020.0000000000000000
+ develop | 9 | 4500 | 5020.0000000000000000
+ develop | 8 | 6000 | 5020.0000000000000000
+ develop | 10 | 5200 | 5020.0000000000000000
+ personnel | 5 | 3500 | 3700.0000000000000000
+ personnel | 2 | 3900 | 3700.0000000000000000
+ sales | 3 | 4800 | 4866.6666666666666667
+ sales | 1 | 5000 | 4866.6666666666666667
+ sales | 4 | 4800 | 4866.6666666666666667
+ (10 rows)
+
+
+ avg works exact same as the aggregate functions
+ except it doesn't reduce rows and returns same result within the
+ same depname. Without reducing rows,
+ it is possible to compare the original salary
+ with each department's average salary.
+
+
+
+ Another expample shows different capability of windowing functions
+ from above.
+
+
+ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
+
+
+
+ depname | empno | salary | rank
+ -----------+-------+--------+------
+ develop | 8 | 6000 | 1
+ develop | 10 | 5200 | 2
+ develop | 11 | 5200 | 2
+ develop | 9 | 4500 | 4
+ develop | 7 | 4200 | 5
+ personnel | 2 | 3900 | 1
+ personnel | 5 | 3500 | 2
+ sales | 1 | 5000 | 1
+ sales | 4 | 4800 | 2
+ sales | 3 | 4800 | 2
+ (10 rows)
+
+
+ rank returns offset position of the row when
+ the rows is ordered by the specified value. In this case,
+ in each department each employee's salary rank with gap is shown.
+
+
+
+ Windowing functions are put in the SELECT list.
+ It is forbidden anywhere else such as GROUP BY,
+ HAVING, WHERE clauses.
+ For the arguments passed to windowing functions and expression in
+ PARTITION BY and ORDER BY
+ in window definition can be the results of aggregate functions.
+ But windowing functions may not be placed as aggregate functions'
+ arguments. All of windowing functions are evaluated after aggregate.
+
+
+
+ In a query, windows can be defined as many as needed. The order
+ of the evaluation for each window is implicitly determined by
+ backend, which means there is no way to predict its order.
+
+
+
+ The same window definitions can be named and put togather into one
+ definition using WINDOWclause.
+
+
+ SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
+
+
+ The two of functions are evaluated in the same window.
+
+
Updates
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***************
*** 26,31 **** SELECT [ ALL | DISTINCT [ ON ( expressioncondition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
+ [ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
***************
*** 469,474 **** HAVING condition
--- 470,507 ----
+
+ <literal>WINDOW</literal> Clause
+
+
+ The optional WINDOW clause has the general form
+
+ WINDOW window_name AS (window_definition) [, ...]
+
+ where window_name is
+ the window name that is referred from windowed functions, and
+ window_definition is described as follows:
+
+
+ [ PARTITION BY expression [, ...] ]
+ [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
+
+ where the first expression is the same as
+ one in GROUP BY and the second is the same as
+ one in ORDER BY of the SELECT.
+ Either of two must be specified.
+
+
+
+ When the window functions with OVER clause in the
+ SELECT list refers to a window name, WINDOW
+ clause must describe what the windowed table is like. If some of the
+ window names are not referred from any of the window function calls,
+ they will be just ignored. In the window definition the expressions
+ are allowed to be aggregated.
+
+
+
<command>SELECT</command> List
*** a/doc/src/sgml/syntax.sgml
--- b/doc/src/sgml/syntax.sgml
***************
*** 1413,1418 **** sqrt(2)
--- 1413,1471 ----
+
+ Windowed Tables
+
+
+ A windowed table is a table with one or more windows.
+ A window is a transient set of rows split within a table
+ described by a window definition. The windowed table allows
+ to process values across multiple rows within the window.
+
+
+
+ The windowed table comes with window function calls.
+
+
+ function_call ([arg]) OVER (PARTITION BY partition_column [ , ... ] ORDER BY order_column [ , ... ])
+ function_call ([arg]) OVER window_name
+
+
+ where arg,
+ partition_column and
+ order_column are normal target
+ list such as table column and subquery result as well as
+ the result of GROUP BY process. Either
+ PARTITION clause or
+ ORDER clause must be specified in a
+ window definition. The window_name
+ in the second form indicates use of a window that is defined
+ later WINDOW clause. In the windowed tables
+ any of aggregate function can be called. Additionally predefined
+ window functions may be used to calculate rank values.
+
+
+
+ The predefined ranking window functions are described in
+ . Currently, window functions
+ cannot be defined by the user.
+
+
+
+ Windowed functions doesn't accept DISTINCT and ALL syntax, even though
+ the function is normal aggregate function. This will be fixed in
+ the later version.
+
+
+
+ Windowed functions are not placed in any of GROUP BY, HAVING and
+ WHERE clauses, which process values before any of the windows. If
+ there is need to qualify rows by the result of windowed functions,
+ whole of the query must be nested and append WHERE clause outer of
+ the current query.
+
+
+
Type Casts
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
***************
*** 617,622 **** explain_outNode(StringInfo str,
--- 617,625 ----
case T_Limit:
pname = "Limit";
break;
+ case T_Window:
+ pname = "Window";
+ break;
case T_Hash:
pname = "Hash";
break;
***************
*** 868,873 **** explain_outNode(StringInfo str,
--- 871,878 ----
show_sort_info((SortState *) planstate,
str, indent, es);
break;
+ case T_Window:
+ break;
case T_Result:
show_upper_qual((List *) ((Result *) plan)->resconstantqual,
"One-Time Filter", plan,
*** a/src/backend/executor/Makefile
--- b/src/backend/executor/Makefile
***************
*** 19,25 **** OBJS = execAmi.o execCurrent.o execGrouping.o execJunk.o execMain.o \
nodeBitmapHeapscan.o nodeBitmapIndexscan.o nodeHash.o \
nodeHashjoin.o nodeIndexscan.o nodeMaterial.o nodeMergejoin.o \
nodeNestloop.o nodeFunctionscan.o nodeResult.o nodeSeqscan.o \
! nodeSetOp.o nodeSort.o nodeUnique.o \
nodeValuesscan.o nodeLimit.o nodeGroup.o \
nodeSubplan.o nodeSubqueryscan.o nodeTidscan.o tstoreReceiver.o spi.o
--- 19,25 ----
nodeBitmapHeapscan.o nodeBitmapIndexscan.o nodeHash.o \
nodeHashjoin.o nodeIndexscan.o nodeMaterial.o nodeMergejoin.o \
nodeNestloop.o nodeFunctionscan.o nodeResult.o nodeSeqscan.o \
! nodeSetOp.o nodeSort.o nodeUnique.o nodeWindow.o\
nodeValuesscan.o nodeLimit.o nodeGroup.o \
nodeSubplan.o nodeSubqueryscan.o nodeTidscan.o tstoreReceiver.o spi.o
*** a/src/backend/executor/execAmi.c
--- b/src/backend/executor/execAmi.c
***************
*** 39,44 ****
--- 39,45 ----
#include "executor/nodeTidscan.h"
#include "executor/nodeUnique.h"
#include "executor/nodeValuesscan.h"
+ #include "executor/nodeWindow.h"
/*
***************
*** 205,210 **** ExecReScan(PlanState *node, ExprContext *exprCtxt)
--- 206,215 ----
ExecReScanLimit((LimitState *) node, exprCtxt);
break;
+ case T_WindowState:
+ ExecReScanWindow((WindowState *) node, exprCtxt);
+ break;
+
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
break;
*** a/src/backend/executor/execProcnode.c
--- b/src/backend/executor/execProcnode.c
***************
*** 103,108 ****
--- 103,109 ----
#include "executor/nodeTidscan.h"
#include "executor/nodeUnique.h"
#include "executor/nodeValuesscan.h"
+ #include "executor/nodeWindow.h"
#include "miscadmin.h"
/* ------------------------------------------------------------------------
***************
*** 261,266 **** ExecInitNode(Plan *node, EState *estate, int eflags)
--- 262,272 ----
estate, eflags);
break;
+ case T_Window:
+ result = (PlanState *) ExecInitWindow((Window *) node,
+ estate, eflags);
+ break;
+
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
result = NULL; /* keep compiler quiet */
***************
*** 410,415 **** ExecProcNode(PlanState *node)
--- 416,425 ----
result = ExecLimit((LimitState *) node);
break;
+ case T_WindowState:
+ result = ExecWindow((WindowState *) node);
+ break;
+
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
result = NULL;
***************
*** 573,578 **** ExecCountSlotsNode(Plan *node)
--- 583,592 ----
case T_Limit:
return ExecCountSlotsLimit((Limit *) node);
+ case T_Window:
+ return ExecCountSlotsWindow((Window *) node);
+ break;
+
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
break;
***************
*** 713,718 **** ExecEndNode(PlanState *node)
--- 727,736 ----
ExecEndLimit((LimitState *) node);
break;
+ case T_WindowState:
+ ExecEndWindow((WindowState *) node);
+ break;
+
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
break;
*** a/src/backend/executor/execQual.c
--- b/src/backend/executor/execQual.c
***************
*** 62,67 **** static Datum ExecEvalArrayRef(ArrayRefExprState *astate,
--- 62,70 ----
static Datum ExecEvalAggref(AggrefExprState *aggref,
ExprContext *econtext,
bool *isNull, ExprDoneCond *isDone);
+ static Datum ExecEvalWinAggref(WinAggrefExprState *winagg,
+ ExprContext *econtext,
+ bool *isNull, ExprDoneCond *isDone);
static Datum ExecEvalVar(ExprState *exprstate, ExprContext *econtext,
bool *isNull, ExprDoneCond *isDone);
static Datum ExecEvalScalarVar(ExprState *exprstate, ExprContext *econtext,
***************
*** 433,438 **** ExecEvalAggref(AggrefExprState *aggref, ExprContext *econtext,
--- 436,465 ----
}
/* ----------------------------------------------------------------
+ * ExecEvalWinAggref
+ *
+ * Returns a Datum whose value is the value of the precomputed
+ * aggregate found in the given expression context.
+ *
+ * Note: exct_winaggvalues may be replaced by exct_aggvalues
+ * because Window node never handles exct_aggvalues currently.
+ * ----------------------------------------------------------------
+ */
+ static Datum
+ ExecEvalWinAggref(WinAggrefExprState *winagg, ExprContext *econtext,
+ bool *isNull, ExprDoneCond *isDone)
+ {
+ if (isDone)
+ *isDone = ExprSingleResult;
+
+ if (econtext->ecxt_winaggvalues == NULL) /* safety check */
+ elog(ERROR, "no aggregates in this expression context");
+
+ *isNull = econtext->ecxt_winaggnulls[winagg->aggno];
+ return econtext->ecxt_winaggvalues[winagg->aggno];
+ }
+
+ /* ----------------------------------------------------------------
* ExecEvalVar
*
* Returns a Datum whose value is the value of a range
***************
*** 3844,3849 **** ExecInitExpr(Expr *node, PlanState *parent)
--- 3871,3901 ----
state = (ExprState *) astate;
}
break;
+ case T_WinAggref:
+ {
+ WinAggref *winagg = (WinAggref *) node;
+ WinAggrefExprState *wstate = makeNode(WinAggrefExprState);
+
+ wstate->xprstate.evalfunc = (ExprStateEvalFunc) ExecEvalWinAggref;
+ if (parent && IsA(parent, WindowState))
+ {
+ WindowState *winstate = (WindowState *) parent;
+ int naggs;
+
+ winstate->aggs = lcons(wstate, winstate->aggs);
+ naggs = ++winstate->numaggs;
+
+ wstate->args = (List *) ExecInitExpr((Expr *) winagg->args,
+ parent);
+ }
+ else
+ {
+ /* planner messed up */
+ elog(ERROR, "winaggref found in non-Window plan node");
+ }
+ state = (ExprState *) wstate;
+ }
+ break;
case T_ArrayRef:
{
ArrayRef *aref = (ArrayRef *) node;
*** /dev/null
--- b/src/backend/executor/nodeWindow.c
***************
*** 0 ****
--- 1,1378 ----
+ /*-------------------------------------------------------------------------
+ *
+ * nodeWindow.c
+ *
+ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * Window node evaluates only WinAggref expression. Since WinAggref is
+ * conceptually derived from Aggref node, Window node resembles Agg node.
+ * Different from Agg node, Window node considers two row sets, Partition
+ * and Frame. Also, contrast to Agg node Window has two key tuples, Partition
+ * and Order, which is close to ranking system.
+ *
+ * Currently Window node assume its input is sorted appropriately so it doesn't
+ * care sort operation.
+ *
+ * A window function is defined as a function that does or does not have a transient
+ * function and a *volatile* final function. Window node will call the final function
+ * per tuple if it is volatile and returns its result as the current row result.
+ * A window aggregate is exactly same as a group aggregate. Since Window node
+ * returns multiple rows for a group (c.f. a frame in Window node), the node
+ * stores its result to avoid multiple calls of final function of aggregate.
+ * If there are only window function and no window aggregate, the node avoids
+ * frame rescan for aggregate trans function, so it handles only initialization
+ * and finalization. As a frame rescan gets considerably high cost, this
+ * optimization is critical in a large frame situation.
+ *
+ * IDENTIFICATION
+ * $Id$
+ *
+ *-------------------------------------------------------------------------
+ */
+
+ #include "postgres.h"
+
+ #include "catalog/pg_aggregate.h"
+ #include "catalog/pg_proc.h"
+ #include "catalog/pg_type.h"
+ #include "executor/executor.h"
+ #include "executor/nodeWindow.h"
+ #include "miscadmin.h"
+ #include "nodes/nodeFuncs.h"
+ #include "optimizer/clauses.h"
+ #include "parser/parse_agg.h"
+ #include "parser/parse_coerce.h"
+ #include "parser/parse_expr.h"
+ #include "parser/parse_oper.h"
+ #include "utils/acl.h"
+ #include "utils/builtins.h"
+ #include "utils/lsyscache.h"
+ #include "utils/memutils.h"
+ #include "utils/syscache.h"
+ #include "utils/tuplestore.h"
+ #include "utils/datum.h"
+
+
+ typedef struct WindowStatePerAggData
+ {
+ /* Links to WinAggref expr and state nodes this working state is for */
+ WinAggrefExprState *winaggstate;
+ WinAggref *winagg;
+
+ /* number of input arguments for aggregate */
+ int numArguments;
+
+ /* Oids of transfer functions */
+ Oid transfn_oid;
+ Oid finalfn_oid; /* may be InvalidOid */
+
+ /*
+ * fmgr lookup data for transfer functions --- only valid when
+ * corresponding oid is not InvalidOid. Note in particular that fn_strict
+ * flags are kept here.
+ */
+ FmgrInfo transfn;
+ FmgrInfo finalfn;
+
+ bool finalfn_volatile; /* volatility of final function */
+
+ /*
+ * initial value from pg_aggregate entry
+ */
+ Datum initValue;
+ bool initValueIsNull;
+
+ /*
+ * We need the len and byval info for the agg's input, result, and
+ * transition data types in order to know how to copy/delete values.
+ */
+ int16 inputtypeLen,
+ resulttypeLen,
+ transtypeLen;
+ bool inputtypeByVal,
+ resulttypeByVal,
+ transtypeByVal;
+ /* DISTINCT argument is not supported for window function so far */
+
+ /*
+ * PerGroupData is here, included in PerAggData, since we do not
+ * support hash strategy so far.
+ */
+ Datum transValue; /* current transition value */
+ bool transValueIsNull;
+
+ bool noTransValue; /* true if transValue not set yet */
+
+ Datum prevValue;
+ bool prevValueIsNull;
+ bool prevCached;
+ void *frameContext;
+ } WindowStatePerAggData;
+
+ /*
+ * ranking process information
+ */
+ typedef struct
+ {
+ int64 rank; /* depending on what type of rank is requested */
+ int64 rowcount; /* total seen row numbers */
+ HeapTuple heaptuple; /* current rank tuple usually */
+ } rank_context;
+
+ /*
+ * ntile process information
+ */
+ typedef struct
+ {
+ int64 ntile; /* current result */
+ int64 nrows; /* row number of current bucket */
+ int64 boundary;
+ int64 remainder;
+ } ntile_context;
+
+ static void win_initialize_aggregates(WindowState *winstate);
+ static void win_advance_transition_function(WindowState *winstate,
+ WindowStatePerAgg peraggstate,
+ FunctionCallInfoData *fcinf);
+ static void win_advance_aggregates(WindowState *winstate);
+ static void win_finalize_aggregate(WindowState *winstate, WindowStatePerAgg peraggstate,
+ Datum *resultVal, bool *resultIsNull);
+ static void store_partition(WindowState *winstate);
+ static TupleTableSlot *process_frame(WindowState *winstate);
+
+ static WindowState *getWindowState(FunctionCallInfo fcinfo);
+ static bool rank_up(FunctionCallInfo fcinfo);
+
+ /*
+ * frame_start -
+ *
+ * initialize frame information. Currently a partition and a frame indicate
+ * almost same meanings, but are conceptually different.
+ * Return value means whether a new frame is created or not.
+ */
+ static bool
+ frame_start(WindowState *winstate)
+ {
+ int numaggs;
+ int i;
+
+ if (winstate->frame_processing)
+ return false;
+
+ numaggs = winstate->numaggs;
+
+ for(i = 0; i < numaggs; i++)
+ {
+ WindowStatePerAgg peraggstate = &winstate->peragg[i];
+
+ peraggstate->prevValueIsNull = true;
+ peraggstate->prevCached = false;
+ peraggstate->frameContext = NULL;
+ if (peraggstate->prevCached && peraggstate->prevValueIsNull &&
+ !peraggstate->resulttypeByVal)
+ {
+ pfree(DatumGetPointer(peraggstate->prevValue));
+ }
+ }
+
+ winstate->frame_processing = true;
+ return true;
+ }
+
+ /*
+ * frame_finish -
+ *
+ * finishes frame.
+ */
+ static void
+ frame_finish(WindowState *winstate)
+ {
+ winstate->partition_processing = false;
+ winstate->frame_processing = false;
+ }
+
+
+ /*
+ * initialize_aggregate -
+ *
+ * Initialize all aggregates for a new group of input values.
+ *
+ * When called, CurrentMemoryContext should be the per-query context.
+ */
+ static void
+ win_initialize_aggregates(WindowState *winstate)
+ {
+ int aggno;
+
+ for (aggno = 0; aggno < winstate->numaggs; aggno++)
+ {
+ WindowStatePerAgg peraggstate = &winstate->peragg[aggno];
+
+ /*
+ * If we are reinitializing after a group boundary, we have to free
+ * any prior transValue to avoid memory leakage. We must check not
+ * only the isnull flag but whether the pointer is NULL;
+ */
+ if (!peraggstate->transtypeByVal &&
+ !peraggstate->transValueIsNull &&
+ DatumGetPointer(peraggstate->transValue) != NULL)
+ pfree(DatumGetPointer(peraggstate->transValue));
+
+ /*
+ * (Re)set transValue to the initial value.
+ *
+ * Note that when the initial value is pass-by-ref, we must copy it
+ * (into the aggcontext) since we will pfree the transValue later.
+ */
+ if (peraggstate->initValueIsNull)
+ peraggstate->transValue = peraggstate->initValue;
+ else
+ {
+ MemoryContext oldContext;
+
+ oldContext = MemoryContextSwitchTo(winstate->wincontext);
+ peraggstate->transValue = datumCopy(peraggstate->initValue,
+ peraggstate->transtypeByVal,
+ peraggstate->transtypeLen);
+ MemoryContextSwitchTo(oldContext);
+ }
+ peraggstate->transValueIsNull = peraggstate->initValueIsNull;
+
+ /*
+ * If the initial value for the transition state doesn't exist in the
+ * pg_aggregate table then we will let the first non-NULL value
+ * returned from the outer procNode become the initial value. (This is
+ * useful for aggregates like max() and min().) The noTransValue flag
+ * signals that we still need to do this.
+ */
+ peraggstate->noTransValue = peraggstate->initValueIsNull;
+ }
+ }
+
+ /*
+ * advance_transition_function -
+ * almost same as the same name function in nodeAgg.c
+ */
+ static void
+ win_advance_transition_function(WindowState *winstate,
+ WindowStatePerAgg peraggstate,
+ FunctionCallInfoData *fcinfo)
+ {
+ int numArguments = peraggstate->numArguments;
+ MemoryContext oldContext;
+ Datum newVal;
+ int i;
+
+ if (peraggstate->transfn.fn_strict)
+ {
+ /*
+ * For a strict transfn, nothing happens when there's a NULL input; we
+ * just keep the prior transValue.
+ */
+ for(i = 1; i <= numArguments; i++)
+ {
+ if (fcinfo->argnull[i])
+ return;
+ }
+
+ if (peraggstate->noTransValue)
+ {
+ /*
+ * transValue has not been initialized. This is the first non-NULL
+ * input value. We use it as the initial value for transValue. (We
+ * already checked that the agg's input type is binary-compatible
+ * with its transtype, so straight copy here is OK.)
+ *
+ * We must copy the datum into aggcontext if it is pass-by-ref. We
+ * do not need to pfree the old transValue, since it's NULL.
+ */
+ oldContext = MemoryContextSwitchTo(winstate->wincontext);
+ peraggstate->transValue = datumCopy(fcinfo->arg[1],
+ peraggstate->transtypeByVal,
+ peraggstate->transtypeLen);
+ peraggstate->transValueIsNull = false;
+ peraggstate->noTransValue = false;
+ MemoryContextSwitchTo(oldContext);
+ return;
+ }
+
+ if (peraggstate->transValueIsNull)
+ {
+ /*
+ * Don't call a strict function with NULL inputs. Note it is
+ * possible to get here despite the above tests, if the transfn is
+ * strict *and* returned a NULL on a prior cycle. If that happens
+ * we will propagate the NULL all the way to the end.
+ */
+ return;
+ }
+ }
+
+ /* We run the transition functions in per-input-tuple memory context */
+ oldContext = MemoryContextSwitchTo(winstate->tmpcontext->ecxt_per_tuple_memory);
+
+ /*
+ * OK to call the transition function
+ */
+ InitFunctionCallInfoData(*fcinfo, &(peraggstate->transfn),
+ numArguments + 1,
+ (void *) winstate, NULL);
+ fcinfo->arg[0] = peraggstate->transValue;
+ fcinfo->argnull[0] = peraggstate->transValueIsNull;
+
+ newVal = FunctionCallInvoke(fcinfo);
+
+ /*
+ * If pass-by-ref datatype, must copy the new value into aggcontext and
+ * pfree the prior transValue. But if transfn returned a pointer to its
+ * first input, we don't need to do anything.
+ */
+ if (!peraggstate->transtypeByVal &&
+ DatumGetPointer(newVal) != DatumGetPointer(peraggstate->transValue))
+ {
+ if (!fcinfo->isnull)
+ {
+ MemoryContextSwitchTo(winstate->wincontext);
+ newVal = datumCopy(newVal,
+ peraggstate->transtypeByVal,
+ peraggstate->transtypeLen);
+ }
+ if (!peraggstate->transValueIsNull)
+ pfree(DatumGetPointer(peraggstate->transValue));
+ }
+
+ peraggstate->transValue = newVal;
+ peraggstate->transValueIsNull = fcinfo->isnull;
+
+ MemoryContextSwitchTo(oldContext);
+ }
+
+ /*
+ * advance_aggregate -
+ */
+ static void
+ win_advance_aggregates(WindowState *winstate)
+ {
+ ExprContext *econtext = winstate->tmpcontext;
+ int aggno;
+
+ for (aggno = 0; aggno < winstate->numaggs; aggno++)
+ {
+ WindowStatePerAgg peraggstate = &winstate->peragg[aggno];
+ WinAggrefExprState *winaggstate = peraggstate->winaggstate;
+ FunctionCallInfoData fcinfo;
+ int i;
+ ListCell *arg;
+ MemoryContext oldContext;
+
+ if (!OidIsValid(peraggstate->transfn_oid))
+ continue;
+
+ /* Switch memory context just once for all args */
+ oldContext = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);
+
+ /* Evaluate inputs and save in fcinfo */
+ /* We start from 1, since the 0th arg will be the transition value */
+ i = 1;
+ foreach(arg, winaggstate->args)
+ {
+ ExprState *argstate = (ExprState *) lfirst(arg);
+
+ fcinfo.arg[i] = ExecEvalExpr(argstate, econtext,
+ fcinfo.argnull + i, NULL);
+ i++;
+ }
+
+ /* Switch back */
+ MemoryContextSwitchTo(oldContext);
+
+ win_advance_transition_function(winstate, peraggstate, &fcinfo);
+ }
+ }
+
+ /*
+ * Compute the final value of one aggregate.
+ *
+ * The finalfunction will be run, and the result delivered, in the
+ * output-tuple context; caller's CurrentMemoryContext does not matter.
+ */
+ static void
+ win_finalize_aggregate(WindowState *winstate,
+ WindowStatePerAgg peraggstate,
+ Datum *resultVal, bool *resultIsNull)
+ {
+ MemoryContext oldContext;
+
+ oldContext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_tuple_memory);
+
+ /*
+ * Apply the agg's finalfn if one is provided, else return transValue.
+ */
+ if (OidIsValid(peraggstate->finalfn_oid))
+ {
+ FunctionCallInfoData fcinfo;
+
+ InitFunctionCallInfoData(fcinfo, &(peraggstate->finalfn), 1,
+ (void *) winstate, NULL);
+ fcinfo.arg[0] = peraggstate->transValue;
+ fcinfo.argnull[0] = peraggstate->transValueIsNull;
+ if (fcinfo.flinfo->fn_strict && peraggstate->transValueIsNull)
+ {
+ /* don't call a strict function with NULL inputs */
+ *resultVal = (Datum) 0;
+ *resultIsNull = true;
+ }
+ else
+ {
+ fcinfo.flinfo->fn_extra = peraggstate->frameContext;
+ *resultVal = FunctionCallInvoke(&fcinfo);
+ *resultIsNull = fcinfo.isnull;
+ /*
+ * finalfn of window function uses this pointer to
+ * keep information.
+ */
+ peraggstate->frameContext = fcinfo.flinfo->fn_extra;
+ }
+ }
+ else
+ {
+ *resultVal = peraggstate->transValue;
+ *resultIsNull = peraggstate->transValueIsNull;
+ }
+
+ if (!peraggstate->resulttypeByVal && !*resultIsNull &&
+ !MemoryContextContains(CurrentMemoryContext,
+ DatumGetPointer(*resultVal)))
+ *resultVal = datumCopy(*resultVal,
+ peraggstate->resulttypeByVal,
+ peraggstate->resulttypeLen);
+
+ MemoryContextSwitchTo(oldContext);
+ }
+
+ /*
+ * process_frame -
+ *
+ * A frame is a set of rows and a unit of aggregate process that is attached with
+ * current row. When new frame is created, we need to compute aggregate again.
+ * Scan through the frame is necessary for aggregate, although not necessary
+ * when there is no aggregate and only window function.
+ * This routine returns final window result.
+ */
+ static TupleTableSlot *
+ process_frame(WindowState *winstate)
+ {
+ ExprContext *econtext;
+ ExprContext *tmpcontext;
+ ProjectionInfo *projInfo;
+ bool forward;
+ Tuplestorestate *ts_partition;
+ TupleTableSlot *scanslot;
+ TupleTableSlot *currentslot;
+ int numaggs = winstate->numaggs;
+ int i;
+
+ currentslot = winstate->currentslot;
+ tmpcontext = winstate->tmpcontext;
+ scanslot = winstate->ss.ss_ScanTupleSlot;
+ econtext = winstate->ss.ps.ps_ExprContext;
+ forward = ScanDirectionIsForward(winstate->ss.ps.state->es_direction);
+ ts_partition = (Tuplestorestate *) winstate->ts_partition;
+
+ /* if first time through, initialize currentslot by cloning input slot */
+ if (currentslot->tts_tupleDescriptor == NULL)
+ {
+ ExecSetSlotDescriptor(currentslot, scanslot->tts_tupleDescriptor);
+ ExecStoreAllNullTuple(currentslot);
+ }
+
+ /*
+ * fetch CURRENT ROW
+ */
+ tuplestore_restorepos(ts_partition);
+
+ if (!tuplestore_gettupleslot(ts_partition, forward, currentslot))
+ {
+ frame_finish(winstate);
+ if (!winstate->win_done)
+ store_partition(winstate);
+ else
+ return NULL;
+ if (!tuplestore_gettupleslot(ts_partition, forward, currentslot))
+ {
+ frame_finish(winstate);
+ return NULL;
+ }
+ }
+ tuplestore_markpos(ts_partition);
+
+ /*
+ * By scanning CURRENT ROW, frame may be changed. If frame is new,
+ * aggregate is restarted. Otherwise, compute final result using
+ * trans value output and cached aggregate result.
+ */
+ if (frame_start(winstate))
+ {
+ /*
+ * a new frame created. Start aggregate from init.
+ */
+ win_initialize_aggregates(winstate);
+
+ /*
+ * If all functions don't have transient function,
+ * just skip.
+ */
+ if (winstate->need_aggregate)
+ {
+ tuplestore_rescan(ts_partition);
+ for(;;)
+ {
+ if (!tuplestore_gettupleslot(ts_partition, forward, scanslot))
+ break;
+
+ /* set up for advance_aggregates call */
+ tmpcontext->ecxt_outertuple = scanslot;
+ win_advance_aggregates(winstate);
+ }
+ }
+ }
+
+ for(i = 0; i < numaggs; i++)
+ {
+ Datum *aggvalue = &econtext->ecxt_winaggvalues[i];
+ bool *aggnull = &econtext->ecxt_winaggnulls[i];
+ WindowStatePerAgg peraggstate = &winstate->peragg[i];
+
+ if (!peraggstate->prevCached ||
+ peraggstate->finalfn_volatile)
+ {
+ /*
+ * case 1: IMMUTABLE
+ * normal aggregate function. if we don't have
+ * final agg result, call final func once and store it.
+ *
+ * case 2: VOLATILE
+ * windowed function.
+ */
+ win_finalize_aggregate(winstate, peraggstate,
+ aggvalue, aggnull);
+
+ if (!peraggstate->finalfn_volatile)
+ {
+ if (!*aggnull)
+ {
+ MemoryContext oldContext;
+ oldContext = MemoryContextSwitchTo(winstate->wincontext);
+ peraggstate->prevValue = datumCopy(*aggvalue,
+ peraggstate->resulttypeByVal,
+ peraggstate->resulttypeLen);
+ MemoryContextSwitchTo(oldContext);
+ }
+ peraggstate->prevValueIsNull = *aggnull;
+ peraggstate->prevCached = true;
+ }
+ }
+ else
+ {
+ /* restore the cache */
+ if (peraggstate->prevValueIsNull)
+ {
+ *aggvalue = (Datum) 0;
+ *aggnull = true;
+ }
+ else
+ {
+ *aggvalue = datumCopy(peraggstate->prevValue,
+ peraggstate->resulttypeByVal,
+ peraggstate->resulttypeLen);
+ *aggnull = false;
+ }
+ }
+ }
+
+ projInfo = winstate->ss.ps.ps_ProjInfo;
+
+ /* Vars in Window all refer to OUTER, which equals CURRENT ROW */
+ econtext->ecxt_outertuple = currentslot;
+
+ return ExecProject(projInfo, NULL);
+ }
+
+ /*
+ * store_partition
+ *
+ * Since frame scrolls in/out and current row is re-scanned, we need to
+ * store whole the partition rows for later random access. This process
+ * is likely to be similar to grouping in nodeAgg.c
+ */
+ static void
+ store_partition(WindowState *winstate)
+ {
+ Window *node = (Window *) winstate->ss.ps.plan;
+ PlanState *outerPlan;
+ ExprContext *econtext;
+ ExprContext *tmpcontext;
+ TupleTableSlot *outerslot;
+ TupleTableSlot *firstSlot;
+ Tuplestorestate *ts_partition;
+
+ outerPlan = outerPlanState(winstate);
+ econtext = winstate->ss.ps.ps_ExprContext;
+ tmpcontext = winstate->tmpcontext;
+ firstSlot = winstate->ss.ss_ScanTupleSlot;
+ ts_partition = (Tuplestorestate *) winstate->ts_partition;
+
+ if (winstate->prt_firstTuple == NULL)
+ {
+ outerslot = ExecProcNode(outerPlan);
+ if (!TupIsNull(outerslot))
+ {
+ winstate->prt_firstTuple = ExecCopySlotTuple(outerslot);
+ }
+ else
+ {
+ winstate->win_done = true;
+ return;
+ }
+ }
+
+ if (winstate->prt_firstTuple != NULL)
+ {
+ ExecStoreTuple(winstate->prt_firstTuple,
+ firstSlot,
+ InvalidBuffer,
+ true);
+ winstate->prt_firstTuple = NULL;
+
+ tmpcontext->ecxt_outertuple = firstSlot;
+
+ if(ts_partition != NULL)
+ {
+ /*
+ * need consider, for this code forgets about final process.
+ */
+ tuplestore_end(ts_partition);
+ }
+ ts_partition = tuplestore_begin_heap(true, false, work_mem);
+ tuplestore_set_eflags(ts_partition,
+ (EXEC_FLAG_REWIND |
+ EXEC_FLAG_BACKWARD |
+ EXEC_FLAG_MARK));
+ winstate->ts_partition = (void *) ts_partition;
+
+ for(;;)
+ {
+ tuplestore_puttupleslot(ts_partition, tmpcontext->ecxt_outertuple);
+
+ outerslot = ExecProcNode(outerPlan);
+ if (TupIsNull(outerslot))
+ {
+ winstate->win_done = true;
+ break;
+ }
+
+ tmpcontext->ecxt_outertuple = outerslot;
+
+ if (!execTuplesMatch(firstSlot,
+ outerslot,
+ node->prtNumCols, node->prtColIdx,
+ winstate->prtEqfunctions,
+ tmpcontext->ecxt_per_tuple_memory))
+ {
+ winstate->prt_firstTuple = ExecCopySlotTuple(outerslot);
+ break;
+ }
+ }
+ }
+
+ winstate->partition_processing = true;
+ }
+
+ /* -----------------
+ * ExecWindow
+ *
+ * Window node execution proceeds as:
+ * 1. store partition
+ * 2. fetch current row
+ * 3. create a frame if the previous frame has finished.
+ * 4. aggregate (trans funcs) if any or if new frame comes.
+ * 5. finalize / recompute result using trans value and context
+ * 6. go to 2. if current partition hasn't finished, to 1. otherwise.
+ * -----------------
+ */
+ TupleTableSlot *
+ ExecWindow(WindowState *winstate)
+ {
+ if (winstate->win_done && !winstate->partition_processing)
+ return NULL;
+
+ if (!winstate->partition_processing)
+ store_partition(winstate);
+
+ return process_frame(winstate);
+ }
+
+ static Datum
+ GetAggInitVal(Datum textInitVal, Oid transtype)
+ {
+ Oid typinput,
+ typioparam;
+ char *strInitVal;
+ Datum initVal;
+
+ getTypeInputInfo(transtype, &typinput, &typioparam);
+ strInitVal = TextDatumGetCString(textInitVal);
+ initVal = OidInputFunctionCall(typinput, strInitVal,
+ typioparam, -1);
+ pfree(strInitVal);
+ return initVal;
+ }
+
+ /* -----------------
+ * ExecInitWindow
+ *
+ * Window node uses an extra TupleTableSlot for current row.
+ * Window aggregate function is as defined in the grouping aggregate,
+ * the initialization of those functions is almost same, contrast to
+ * that the other part of functions, window functions, are formed as
+ * aggregate function (i.e. pg_aggregate holds their information) but
+ * have volatile final function. Also, the trans function may be null
+ * with these functions.
+ * -----------------
+ */
+ WindowState *
+ ExecInitWindow(Window *node, EState *estate, int eflags)
+ {
+ WindowState *winstate;
+ Plan *outerPlan;
+ ExprContext *econtext;
+ WindowStatePerAgg peragg;
+ int numaggs = 0, aggno;
+ bool need_aggregate;
+ ListCell *l;
+
+ winstate = makeNode(WindowState);
+ winstate->ss.ps.plan = (Plan *) node;
+ winstate->ss.ps.state = estate;
+ winstate->eflags = (eflags &
+ (EXEC_FLAG_REWIND | EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK));
+
+ /*
+ * Create expression contexts. We need two, one for per-input-tuple
+ * processing and one for per-output-tuple processing. We cheat a little
+ * by using ExecAssignExprContext() to build both.
+ */
+ ExecAssignExprContext(estate, &winstate->ss.ps);
+ winstate->tmpcontext = winstate->ss.ps.ps_ExprContext;
+ ExecAssignExprContext(estate, &winstate->ss.ps);
+
+ winstate->wincontext =
+ AllocSetContextCreate(CurrentMemoryContext,
+ "WinContext",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
+ #define WINDOW_NSLOTS 3
+
+ /*
+ * tuple table initialization
+ */
+ ExecInitScanTupleSlot(estate, &winstate->ss);
+ ExecInitResultTupleSlot(estate, &winstate->ss.ps);
+ winstate->currentslot = ExecInitExtraTupleSlot(estate);
+
+ winstate->ss.ps.targetlist = (List *)
+ ExecInitExpr((Expr *) node->plan.targetlist,
+ (PlanState *) winstate);
+ winstate->ss.ps.qual = NIL;
+
+ /*
+ * initialize child nodes
+ *
+ * We shield the child node from the need to support REWIND, BACKWARD, or
+ * MARK/RESTORE.
+ */
+ eflags &= ~(EXEC_FLAG_REWIND | EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK);
+ outerPlan = outerPlan(node);
+ outerPlanState(winstate) = ExecInitNode(outerPlan, estate, eflags);
+
+ /*
+ * initialize result tuple type and projection info.
+ */
+ ExecAssignScanTypeFromOuterPlan(&winstate->ss);
+
+ /*
+ * initialize result tuple type and projection info.
+ */
+ ExecAssignResultTypeFromTL(&winstate->ss.ps);
+ ExecAssignProjectionInfo(&winstate->ss.ps, NULL);
+
+ numaggs = winstate->numaggs;
+ if (node->prtNumCols > 0)
+ winstate->prtEqfunctions = execTuplesMatchPrepare(node->prtNumCols,
+ node->prtOperators);
+ if (node->ordNumCols > 0)
+ winstate->ordEqfunctions = execTuplesMatchPrepare(node->ordNumCols,
+ node->ordOperators);
+
+ econtext = winstate->ss.ps.ps_ExprContext;
+ econtext->ecxt_winaggvalues = (Datum *) palloc0(sizeof(Datum) * numaggs);
+ econtext->ecxt_winaggnulls = (bool *) palloc0(sizeof(bool) * numaggs);
+
+ peragg = (WindowStatePerAgg) palloc0(sizeof(WindowStatePerAggData) * numaggs);
+ winstate->peragg = peragg;
+
+ aggno = -1;
+ need_aggregate = false;
+ foreach(l, winstate->aggs)
+ {
+ WinAggrefExprState *winaggstate = (WinAggrefExprState *) lfirst(l);
+ WinAggref *winagg = (WinAggref *) winaggstate->xprstate.expr;
+ WindowStatePerAgg peraggstate;
+ Oid inputTypes[FUNC_MAX_ARGS];
+ int numArguments;
+ HeapTuple aggTuple;
+ Form_pg_aggregate aggform;
+ Oid aggtranstype;
+ AclResult aclresult;
+ Oid transfn_oid;
+ Oid finalfn_oid;
+ Expr *transfnexpr;
+ Expr *finalfnexpr;
+ Datum textInitVal;
+ int i;
+ ListCell *lc;
+
+ Assert(winagg->agglevelsup == 0);
+
+ /* Look for a previous duplicate aggregate */
+ for (i = 0; i <= aggno; i++)
+ {
+ if (equal(winagg, peragg[i].winagg) &&
+ !contain_volatile_functions((Node *) winagg))
+ break;
+ }
+ if (i <= aggno)
+ {
+ /* Found a match to an existing entry, so just mark it */
+ winaggstate->aggno = i;
+ continue;
+ }
+
+ /* Nope, so assign a new PerAgg record */
+ peraggstate = &peragg[++aggno];
+
+ /* Mark WinAggref state node with assigned index in the result array */
+ winaggstate->aggno = aggno;
+
+ /* Fill in the peraggstate data */
+ peraggstate->winaggstate = winaggstate;
+ peraggstate->winagg = winagg;
+ numArguments = list_length(winagg->args);
+ peraggstate->numArguments = numArguments;
+
+ /*
+ * Get actual datatypes of the inputs. These could be different from
+ * the agg's declared input types, when the agg accepts ANY or a
+ * polymorphic type.
+ */
+ i = 0;
+ foreach(lc, winagg->args)
+ {
+ inputTypes[i++] = exprType((Node *) lfirst(lc));
+ }
+
+ aggTuple = SearchSysCache(AGGFNOID,
+ ObjectIdGetDatum(winagg->aggfnoid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(aggTuple))
+ elog(ERROR, "cache lookup failed for window aggregate %u",
+ winagg->aggfnoid);
+ aggform = (Form_pg_aggregate) GETSTRUCT(aggTuple);
+
+ /* Check permission to call aggregate function */
+ aclresult = pg_proc_aclcheck(winagg->aggfnoid, GetUserId(),
+ ACL_EXECUTE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_PROC,
+ get_func_name(winagg->aggfnoid));
+
+ peraggstate->transfn_oid = transfn_oid = aggform->aggtransfn;
+ peraggstate->finalfn_oid = finalfn_oid = aggform->aggfinalfn;
+
+ /*
+ * If final func is volatile, it is a window function, not aggregate.
+ */
+ if (OidIsValid(finalfn_oid) && func_volatile(finalfn_oid) == PROVOLATILE_VOLATILE)
+ peraggstate->finalfn_volatile = true;
+
+ /* Check that aggregate owner has permission to call component fns */
+ {
+ HeapTuple procTuple;
+ Oid aggOwner;
+
+ procTuple = SearchSysCache(PROCOID,
+ ObjectIdGetDatum(winagg->aggfnoid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(procTuple))
+ elog(ERROR, "cache lookup failed for function %u",
+ winagg->aggfnoid);
+ aggOwner = ((Form_pg_proc) GETSTRUCT(procTuple))->proowner;
+ ReleaseSysCache(procTuple);
+
+ if (OidIsValid(transfn_oid))
+ {
+ aclresult = pg_proc_aclcheck(transfn_oid, aggOwner,
+ ACL_EXECUTE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_PROC,
+ get_func_name(transfn_oid));
+ }
+
+ if (OidIsValid(finalfn_oid))
+ {
+ aclresult = pg_proc_aclcheck(finalfn_oid, aggOwner,
+ ACL_EXECUTE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_PROC,
+ get_func_name(finalfn_oid));
+ }
+ }
+
+ /* resolve actual type of transition state, if polymorphic */
+ aggtranstype = aggform->aggtranstype;
+ if (IsPolymorphicType(aggtranstype))
+ {
+ /* have to fetch the agg's declared input types... */
+ Oid *declaredArgTypes;
+ int agg_nargs;
+
+ (void) get_func_signature(winagg->aggfnoid,
+ &declaredArgTypes, &agg_nargs);
+ Assert(agg_nargs == numArguments);
+ aggtranstype = enforce_generic_type_consistency(inputTypes,
+ declaredArgTypes,
+ agg_nargs,
+ aggtranstype,
+ false);
+ pfree(declaredArgTypes);
+ }
+
+ /* build expression trees using actual argument & result types */
+ build_aggregate_fnexprs(inputTypes,
+ numArguments,
+ aggtranstype,
+ winagg->aggtype,
+ transfn_oid,
+ finalfn_oid,
+ &transfnexpr,
+ &finalfnexpr);
+
+ if (OidIsValid(transfn_oid))
+ {
+ fmgr_info(transfn_oid, &peraggstate->transfn);
+ peraggstate->transfn.fn_expr = (Node *) transfnexpr;
+ /*
+ * If at least one of window functions is an aggregate,
+ * we must process iteration for aggregate. Otherwise,
+ * it can be passed.
+ */
+ need_aggregate = true;
+ }
+
+ if (OidIsValid(finalfn_oid))
+ {
+ fmgr_info(finalfn_oid, &peraggstate->finalfn);
+ peraggstate->finalfn.fn_expr = (Node *) finalfnexpr;
+ }
+
+ get_typlenbyval(winagg->aggtype,
+ &peraggstate->resulttypeLen,
+ &peraggstate->resulttypeByVal);
+ get_typlenbyval(aggtranstype,
+ &peraggstate->transtypeLen,
+ &peraggstate->transtypeByVal);
+
+ /*
+ * initval is potentially null, so don't try to access it as a struct
+ * field. Must do it the hard way with SysCacheGetAttr.
+ */
+ textInitVal = SysCacheGetAttr(AGGFNOID, aggTuple,
+ Anum_pg_aggregate_agginitval,
+ &peraggstate->initValueIsNull);
+
+ if (peraggstate->initValueIsNull)
+ peraggstate->initValue = (Datum) 0;
+ else
+ peraggstate->initValue = GetAggInitVal(textInitVal,
+ aggtranstype);
+
+ /*
+ * If the transfn is strict and the initval is NULL, make sure input
+ * type and transtype are the same (or at least binary-compatible), so
+ * that it's OK to use the first input value as the initial
+ * transValue. This should have been checked at agg definition time,
+ * but just in case...
+ */
+ if (peraggstate->transfn.fn_strict && peraggstate->initValueIsNull)
+ {
+ if (numArguments < 1 ||
+ !IsBinaryCoercible(inputTypes[0], aggtranstype))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
+ errmsg("aggregate %u needs to have compatible input type and transition type",
+ winagg->aggfnoid)));
+ }
+
+ ReleaseSysCache(aggTuple);
+ }
+
+ /* Update numaggs to match number of unique aggregates found */
+ winstate->numaggs = aggno + 1;
+ winstate->need_aggregate = need_aggregate;
+
+ return winstate;
+ }
+
+ /* -----------------
+ * ExecCountSlotsWindow
+ * -----------------
+ */
+ int
+ ExecCountSlotsWindow(Window *node)
+ {
+ return ExecCountSlotsNode(outerPlan(node)) +
+ ExecCountSlotsNode(innerPlan(node)) +
+ WINDOW_NSLOTS;
+ }
+ /* -----------------
+ * ExecEndWindow
+ * -----------------
+ */
+ void
+ ExecEndWindow(WindowState *node)
+ {
+ PlanState *outerPlan;
+
+ /*
+ * Free both the expr contexts.
+ */
+ ExecFreeExprContext(&node->ss.ps);
+ node->ss.ps.ps_ExprContext = node->tmpcontext;
+ ExecFreeExprContext(&node->ss.ps);
+
+ ExecClearTuple(node->ss.ss_ScanTupleSlot);
+
+ MemoryContextDelete(node->wincontext);
+
+ if(node->ts_partition)
+ {
+ tuplestore_end((Tuplestorestate *) node->ts_partition);
+ node->ts_partition = NULL;
+ }
+
+ outerPlan = outerPlanState(node);
+ ExecEndNode(outerPlan);
+ }
+
+ /* -----------------
+ * ExecRescanWindow
+ * -----------------
+ */
+ void
+ ExecReScanWindow(WindowState *node, ExprContext *exprCtxt)
+ {
+ ExprContext *econtext = node->ss.ps.ps_ExprContext;
+ int i;
+
+ node->win_done = false;
+
+ if (node->prt_firstTuple != NULL)
+ {
+ heap_freetuple(node->prt_firstTuple);
+ node->prt_firstTuple = NULL;
+ }
+
+ MemSet(econtext->ecxt_winaggvalues, 0, sizeof(Datum) * node->numaggs);
+ MemSet(econtext->ecxt_winaggnulls, 0, sizeof(bool) * node->numaggs);
+
+ frame_finish(node);
+ for(i = 0; i < node->numaggs; i++)
+ {
+ WindowStatePerAgg peraggstate = &node->peragg[i];
+
+ if (!peraggstate->transtypeByVal &&
+ !peraggstate->transValueIsNull &&
+ DatumGetPointer(peraggstate->transValue) != NULL)
+ {
+ pfree(DatumGetPointer(peraggstate->transValue));
+ peraggstate->transValueIsNull = true;
+ peraggstate->noTransValue = true;
+ }
+ }
+
+ MemoryContextResetAndDeleteChildren(node->wincontext);
+
+ ExecReScan(((PlanState *) node)->lefttree, exprCtxt);
+ }
+
+ /*
+ * below are finals and related for window functions.
+ */
+ static WindowState *
+ getWindowState(FunctionCallInfo fcinfo)
+ {
+ WindowState *winstate;
+
+ if (!IsA(fcinfo->context, WindowState))
+ elog(ERROR, "Window context is needed for this function");
+
+ winstate = (WindowState *) fcinfo->context;
+
+ return winstate;
+ }
+
+ #define allocate_if_new(fcinfo, size) do{ \
+ if ((fcinfo)->flinfo->fn_extra == NULL) \
+ { \
+ MemoryContext __oldContext; \
+ __oldContext = MemoryContextSwitchTo((fcinfo)->flinfo->fn_mcxt); \
+ (fcinfo)->flinfo->fn_extra = palloc0(size); \
+ MemoryContextSwitchTo(__oldContext); \
+ } \
+ }while(0)
+
+ static bool
+ rank_up(FunctionCallInfo fcinfo)
+ {
+ WindowState *winstate;
+ Window *node;
+ TupleTableSlot *slot;
+ TupleTableSlot *currentslot;
+ MemoryContext oldContext;
+ rank_context *context;
+ bool up = false; /* should rank up? */
+
+ allocate_if_new(fcinfo, sizeof(rank_context));
+
+ context = (rank_context *) fcinfo->flinfo->fn_extra;
+ winstate = getWindowState(fcinfo);
+ node = (Window *) winstate->ss.ps.plan;
+ currentslot = winstate->currentslot;
+
+ if (context->heaptuple == NULL)
+ {
+ /* first call */
+ oldContext = MemoryContextSwitchTo(fcinfo->flinfo->fn_mcxt);
+ context->heaptuple = ExecCopySlotTuple(currentslot);
+ context->rank = context->rowcount = 1;
+ MemoryContextSwitchTo(oldContext);
+ }
+ else
+ {
+ if (!node->ordNumCols)
+ elog(ERROR, "this function requires ORDER BY clause in the window");
+ slot = winstate->ss.ss_ScanTupleSlot;
+ ExecStoreTuple(context->heaptuple, slot, InvalidBuffer, false);
+
+ if (!execTuplesMatch(slot, currentslot,
+ node->ordNumCols, node->ordColIdx,
+ winstate->ordEqfunctions,
+ winstate->tmpcontext->ecxt_per_tuple_memory))
+ {
+ up = true;
+ oldContext = MemoryContextSwitchTo(fcinfo->flinfo->fn_mcxt);
+ context->heaptuple = ExecCopySlotTuple(currentslot);
+ MemoryContextSwitchTo(oldContext);
+ }
+ context->rowcount += 1;
+ }
+
+ return up;
+ }
+
+ /*
+ * row_number
+ * just increment up from 1 until current context finishes.
+ */
+ Datum
+ row_number_final(PG_FUNCTION_ARGS)
+ {
+ int64 *counter = NULL;
+
+ allocate_if_new(fcinfo, sizeof(int64));
+
+ counter = (int64 *) fcinfo->flinfo->fn_extra;
+ *counter = *counter + 1;
+
+ PG_RETURN_INT64(*counter);
+ }
+
+ /*
+ * rank
+ * increment up if key tuple changes. The new rank number is as the current row number.
+ */
+ Datum
+ rank_final(PG_FUNCTION_ARGS)
+ {
+ rank_context *context;
+ bool up;
+
+ up = rank_up(fcinfo);
+ context = (rank_context *) fcinfo->flinfo->fn_extra;
+ if (up)
+ {
+ context->rank = context->rowcount;
+ }
+
+ return Int64GetDatumFast(context->rank);
+ }
+
+ /*
+ * increment up if key tuple changes. The new rank number is as added up 1.
+ */
+ Datum
+ dense_rank_final(PG_FUNCTION_ARGS)
+ {
+ rank_context *context;
+ bool up;
+
+ up = rank_up(fcinfo);
+ context = (rank_context *) fcinfo->flinfo->fn_extra;
+ if (up)
+ {
+ context->rank += 1;
+ }
+
+ PG_RETURN_INT64(context->rank);
+ }
+
+ /*
+ * percent_rank returns fraction between 0 and 1 inclusive, which
+ * is described as (RK - 1) / (NR - 1), where RK is the rank and NR is
+ * the number of total row.
+ */
+ Datum
+ percent_rank_final(PG_FUNCTION_ARGS)
+ {
+ rank_context *context;
+ bool up;
+ int64 total_rows;
+
+ up = rank_up(fcinfo);
+ context = (rank_context *) fcinfo->flinfo->fn_extra;
+ if (up)
+ {
+ context->rank = context->rowcount;
+ }
+
+ total_rows = PG_GETARG_INT64(0);
+ if(total_rows == 1)
+ PG_RETURN_FLOAT8(1.0);
+
+ PG_RETURN_FLOAT8((float8) (context->rank - 1) / (float8) (total_rows - 1));
+ }
+
+ /*
+ * cume_dist
+ * return fraction betweeen 0 and 1 inclusive, which
+ * is described as NP / NR, where NP is the number of row preceeding and
+ * NR is the number of total row.
+ */
+ Datum
+ cume_dist_final(PG_FUNCTION_ARGS)
+ {
+ rank_context *context;
+ bool up;
+ int64 total_rows;
+
+ up = rank_up(fcinfo);
+ context = (rank_context *) fcinfo->flinfo->fn_extra;
+
+ total_rows = PG_GETARG_INT64(0);
+
+ PG_RETURN_FLOAT8((float8) context->rowcount / (float8) total_rows);
+ }
+
+ /*
+ * ntile(integer) divide each row by bucket.
+ * Since if total row count is not divisible by the argument integer
+ * it adds one row to the leading buckets, number of difference between
+ * maximum bucket's row and minimum bucket's row must be one or zero.
+ */
+ Datum
+ ntile_trans(PG_FUNCTION_ARGS)
+ {
+ ArrayType *data;
+ int64 *elements;
+ int64 nbuckets;
+
+ data = PG_GETARG_ARRAYTYPE_P(0);
+ elements = (int64 *) ARR_DATA_PTR(data);
+ elements[0] += 1;
+ if (elements[1] == 0)
+ {
+ nbuckets = PG_GETARG_INT64(1);
+ if (nbuckets <= 0)
+ elog(ERROR, "negative or zero bucket size detected");
+ elements[1] = nbuckets;
+ }
+
+ PG_RETURN_ARRAYTYPE_P(data);
+ }
+
+ Datum
+ ntile_final(PG_FUNCTION_ARGS)
+ {
+ ntile_context *context;
+
+ allocate_if_new(fcinfo, sizeof(ntile_context));
+
+ context = (ntile_context *) fcinfo->flinfo->fn_extra;
+
+ if (context->ntile == 0)
+ {
+ /* first call */
+ ArrayType *data;
+ int64 *elements;
+ int64 total;
+ int64 nbuckets;
+
+ data = PG_GETARG_ARRAYTYPE_P(0);
+ elements = (int64 *) ARR_DATA_PTR(data);
+ total = elements[0];
+ nbuckets = elements[1];
+
+ context->ntile = 1;
+ context->nrows = 0;
+ context->boundary = total / nbuckets;
+ if (context->boundary <= 0)
+ context->boundary = 1;
+ else
+ {
+ /*
+ * If the total number is not divisible, add 1 row to
+ * leading buckets.
+ */
+ context->remainder = total % nbuckets;
+ if (context->remainder != 0)
+ context->boundary += 1;
+ }
+ }
+
+ context->nrows += 1;
+ if (context->boundary < context->nrows)
+ {
+ if (context->remainder != 0 && context->ntile == context->remainder)
+ {
+ context->remainder = 0;
+ context->boundary -= 1;
+ }
+ context->ntile += 1;
+ context->nrows = 1;
+ }
+
+ PG_RETURN_INT64(context->ntile);
+ }
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 689,694 **** _copyLimit(Limit *from)
--- 689,717 ----
return newnode;
}
+ /*
+ * _copyWindow
+ */
+ static Window *
+ _copyWindow(Window *from)
+ {
+ Window *newnode = makeNode(Window);
+
+ CopyPlanFields((Plan *) from, (Plan *) newnode);
+
+ COPY_SCALAR_FIELD(prtNumCols);
+ COPY_POINTER_FIELD(prtColIdx, from->prtNumCols * sizeof(AttrNumber));
+ COPY_POINTER_FIELD(prtOperators, from->prtNumCols * sizeof(Oid));
+ COPY_SCALAR_FIELD(ordNumCols);
+ COPY_POINTER_FIELD(ordColIdx, from->ordNumCols * sizeof(AttrNumber));
+ COPY_NODE_FIELD(preceding);
+ COPY_NODE_FIELD(following);
+ COPY_SCALAR_FIELD(winref);
+
+ return newnode;
+ }
+
+
/* ****************************************************************
* primnodes.h copy functions
* ****************************************************************
***************
*** 844,849 **** _copyAggref(Aggref *from)
--- 867,892 ----
}
/*
+ * _copyWinAggref
+ */
+ static WinAggref *
+ _copyWinAggref(WinAggref *from)
+ {
+ WinAggref *newnode = makeNode(WinAggref);
+
+ COPY_SCALAR_FIELD(aggfnoid);
+ COPY_SCALAR_FIELD(aggtype);
+ COPY_NODE_FIELD(args);
+ COPY_SCALAR_FIELD(agglevelsup);
+ COPY_SCALAR_FIELD(aggstar);
+ COPY_SCALAR_FIELD(aggdistinct);
+ COPY_SCALAR_FIELD(winref);
+ COPY_LOCATION_FIELD(location);
+
+ return newnode;
+ }
+
+ /*
* _copyArrayRef
*/
static ArrayRef *
***************
*** 1603,1608 **** _copySortGroupClause(SortGroupClause *from)
--- 1646,1704 ----
return newnode;
}
+ static OrderClause *
+ _copyOrderClause(OrderClause *from)
+ {
+ OrderClause *newnode = makeNode(OrderClause);
+
+ COPY_SCALAR_FIELD(tleSortGroupRef);
+ COPY_SCALAR_FIELD(eqop);
+ COPY_SCALAR_FIELD(sortop);
+ COPY_SCALAR_FIELD(nulls_first);
+
+ return newnode;
+ }
+
+ static PartitionClause *
+ _copyPartitionClause(PartitionClause *from)
+ {
+ PartitionClause *newnode = makeNode(PartitionClause);
+
+ COPY_SCALAR_FIELD(tleSortGroupRef);
+ COPY_SCALAR_FIELD(eqop);
+ COPY_SCALAR_FIELD(sortop);
+ COPY_SCALAR_FIELD(nulls_first);
+
+ return newnode;
+ }
+
+ static WinDef *
+ _copyWinDef(WinDef *from)
+ {
+ WinDef *newnode = makeNode(WinDef);
+
+ COPY_NODE_FIELD(partitionClause);
+ COPY_NODE_FIELD(orderClause);
+ COPY_NODE_FIELD(expr_list);
+ COPY_STRING_FIELD(name);
+ COPY_LOCATION_FIELD(location);
+
+ return newnode;
+ }
+
+ static WindowClause *
+ _copyWindowClause(WindowClause *from)
+ {
+ WindowClause *newnode = makeNode(WindowClause);
+
+ COPY_NODE_FIELD(partitionClause);
+ COPY_NODE_FIELD(orderClause);
+ COPY_STRING_FIELD(name);
+ COPY_SCALAR_FIELD(winref);
+
+ return newnode;
+ }
+
static RowMarkClause *
_copyRowMarkClause(RowMarkClause *from)
{
***************
*** 1692,1697 **** _copyFuncCall(FuncCall *from)
--- 1788,1794 ----
COPY_SCALAR_FIELD(agg_star);
COPY_SCALAR_FIELD(agg_distinct);
COPY_SCALAR_FIELD(func_variadic);
+ COPY_NODE_FIELD(win_definition);
COPY_LOCATION_FIELD(location);
return newnode;
***************
*** 1905,1910 **** _copyQuery(Query *from)
--- 2002,2008 ----
COPY_SCALAR_FIELD(hasAggs);
COPY_SCALAR_FIELD(hasSubLinks);
COPY_SCALAR_FIELD(hasDistinctOn);
+ COPY_SCALAR_FIELD(hasWindow);
COPY_NODE_FIELD(rtable);
COPY_NODE_FIELD(jointree);
COPY_NODE_FIELD(targetList);
***************
*** 1913,1918 **** _copyQuery(Query *from)
--- 2011,2017 ----
COPY_NODE_FIELD(havingQual);
COPY_NODE_FIELD(distinctClause);
COPY_NODE_FIELD(sortClause);
+ COPY_NODE_FIELD(windowList);
COPY_NODE_FIELD(limitOffset);
COPY_NODE_FIELD(limitCount);
COPY_NODE_FIELD(rowMarks);
***************
*** 1973,1978 **** _copySelectStmt(SelectStmt *from)
--- 2072,2078 ----
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(groupClause);
COPY_NODE_FIELD(havingClause);
+ COPY_NODE_FIELD(windowClause);
COPY_NODE_FIELD(valuesLists);
COPY_NODE_FIELD(sortClause);
COPY_NODE_FIELD(limitOffset);
***************
*** 3147,3152 **** copyObject(void *from)
--- 3247,3255 ----
case T_Limit:
retval = _copyLimit(from);
break;
+ case T_Window:
+ retval = _copyWindow(from);
+ break;
/*
* PRIMITIVE NODES
***************
*** 3172,3177 **** copyObject(void *from)
--- 3275,3283 ----
case T_Aggref:
retval = _copyAggref(from);
break;
+ case T_WinAggref:
+ retval = _copyWinAggref(from);
+ break;
case T_ArrayRef:
retval = _copyArrayRef(from);
break;
***************
*** 3637,3642 **** copyObject(void *from)
--- 3743,3760 ----
case T_SortGroupClause:
retval = _copySortGroupClause(from);
break;
+ case T_OrderClause:
+ retval = _copyOrderClause(from);
+ break;
+ case T_PartitionClause:
+ retval = _copyPartitionClause(from);
+ break;
+ case T_WinDef:
+ retval = _copyWinDef(from);
+ break;
+ case T_WindowClause:
+ retval = _copyWindowClause(from);
+ break;
case T_RowMarkClause:
retval = _copyRowMarkClause(from);
break;
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 191,196 **** _equalAggref(Aggref *a, Aggref *b)
--- 191,211 ----
}
static bool
+ _equalWinAggref(WinAggref *a, WinAggref *b)
+ {
+ COMPARE_SCALAR_FIELD(aggfnoid);
+ COMPARE_SCALAR_FIELD(aggtype);
+ COMPARE_NODE_FIELD(args);
+ COMPARE_SCALAR_FIELD(agglevelsup);
+ COMPARE_SCALAR_FIELD(aggstar);
+ COMPARE_SCALAR_FIELD(aggdistinct);
+ COMPARE_SCALAR_FIELD(winref);
+ COMPARE_LOCATION_FIELD(location);
+
+ return true;
+ }
+
+ static bool
_equalArrayRef(ArrayRef *a, ArrayRef *b)
{
COMPARE_SCALAR_FIELD(refarraytype);
***************
*** 867,872 **** _equalSelectStmt(SelectStmt *a, SelectStmt *b)
--- 882,888 ----
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(groupClause);
COMPARE_NODE_FIELD(havingClause);
+ COMPARE_NODE_FIELD(windowClause);
COMPARE_NODE_FIELD(valuesLists);
COMPARE_NODE_FIELD(sortClause);
COMPARE_NODE_FIELD(limitOffset);
***************
*** 1760,1765 **** _equalFuncCall(FuncCall *a, FuncCall *b)
--- 1776,1782 ----
COMPARE_SCALAR_FIELD(agg_star);
COMPARE_SCALAR_FIELD(agg_distinct);
COMPARE_SCALAR_FIELD(func_variadic);
+ COMPARE_NODE_FIELD(win_definition);
COMPARE_LOCATION_FIELD(location);
return true;
***************
*** 1952,1957 **** _equalSortGroupClause(SortGroupClause *a, SortGroupClause *b)
--- 1969,1997 ----
}
static bool
+ _equalWinDef(WinDef *a, WinDef *b)
+ {
+ COMPARE_NODE_FIELD(partitionClause);
+ COMPARE_NODE_FIELD(orderClause);
+ COMPARE_NODE_FIELD(expr_list);
+ COMPARE_STRING_FIELD(name);
+ COMPARE_LOCATION_FIELD(location);
+
+ return true;
+ }
+
+ static bool
+ _equalWindowClause(WindowClause *a, WindowClause *b)
+ {
+ COMPARE_NODE_FIELD(partitionClause);
+ COMPARE_NODE_FIELD(orderClause);
+ COMPARE_STRING_FIELD(name);
+ COMPARE_SCALAR_FIELD(winref);
+
+ return true;
+ }
+
+ static bool
_equalRowMarkClause(RowMarkClause *a, RowMarkClause *b)
{
COMPARE_SCALAR_FIELD(rti);
***************
*** 2127,2132 **** equal(void *a, void *b)
--- 2167,2174 ----
break;
case T_Aggref:
retval = _equalAggref(a, b);
+ case T_WinAggref:
+ retval = _equalWinAggref(a, b);
break;
case T_ArrayRef:
retval = _equalArrayRef(a, b);
***************
*** 2233,2238 **** equal(void *a, void *b)
--- 2275,2283 ----
case T_JoinExpr:
retval = _equalJoinExpr(a, b);
break;
+ case T_WindowClause:
+ retval = _equalWindowClause(a, b);
+ break;
/*
* RELATION NODES
***************
*** 2577,2584 **** equal(void *a, void *b)
--- 2622,2634 ----
retval = _equalRangeTblEntry(a, b);
break;
case T_SortGroupClause:
+ case T_OrderClause:
+ case T_PartitionClause:
retval = _equalSortGroupClause(a, b);
break;
+ case T_WinDef:
+ retval = _equalWinDef(a, b);
+ break;
case T_RowMarkClause:
retval = _equalRowMarkClause(a, b);
break;
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 52,57 **** exprType(Node *expr)
--- 52,60 ----
case T_Aggref:
type = ((Aggref *) expr)->aggtype;
break;
+ case T_WinAggref:
+ type = ((WinAggref *) expr)->aggtype;
+ break;
case T_ArrayRef:
{
ArrayRef *arrayref = (ArrayRef *) expr;
***************
*** 1019,1024 **** expression_tree_walker(Node *node,
--- 1022,1035 ----
return true;
}
break;
+ case T_WinAggref:
+ {
+ WinAggref *expr = (WinAggref *)node;
+ if(expression_tree_walker((Node *) expr->args,
+ walker, context))
+ return true;
+ }
+ break;
case T_ArrayRef:
{
ArrayRef *aref = (ArrayRef *) node;
***************
*** 1487,1492 **** expression_tree_mutator(Node *node,
--- 1498,1513 ----
return (Node *) newnode;
}
break;
+ case T_WinAggref:
+ {
+ WinAggref *winagg = (WinAggref *) node;
+ WinAggref *newnode;
+
+ FLATCOPY(newnode, winagg, WinAggref);
+ MUTATE(newnode->args, winagg->args, List *);
+ return (Node *) newnode;
+ }
+ break;
case T_ArrayRef:
{
ArrayRef *arrayref = (ArrayRef *) node;
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 631,636 **** _outLimit(StringInfo str, Limit *node)
--- 631,658 ----
}
static void
+ _outWindow(StringInfo str, Window *node)
+ {
+ int i;
+
+ WRITE_NODE_TYPE("WINDOW");
+
+ _outPlanInfo(str, (Plan *) node);
+
+ appendStringInfo(str, " :prtColIdx");
+ for (i = 0; i < node->prtNumCols; i++)
+ appendStringInfo(str, " %d", node->prtColIdx[i]);
+
+ appendStringInfo(str, " :prtOperations");
+ for (i = 0; i < node->prtNumCols; i++)
+ appendStringInfo(str, " %d", node->prtOperators[i]);
+
+ appendStringInfo(str, " :ordColIdx");
+ for (i = 0; i< node->ordNumCols; i++)
+ appendStringInfo(str, " %d", node->ordColIdx[i]);
+ }
+
+ static void
_outHash(StringInfo str, Hash *node)
{
WRITE_NODE_TYPE("HASH");
***************
*** 742,747 **** _outAggref(StringInfo str, Aggref *node)
--- 764,784 ----
}
static void
+ _outWinAggref(StringInfo str, WinAggref *node)
+ {
+ WRITE_NODE_TYPE("WINAGGREF");
+
+ WRITE_OID_FIELD(aggfnoid);
+ WRITE_OID_FIELD(aggtype);
+ WRITE_NODE_FIELD(args);
+ WRITE_UINT_FIELD(agglevelsup);
+ WRITE_BOOL_FIELD(aggstar);
+ WRITE_BOOL_FIELD(aggdistinct);
+ WRITE_UINT_FIELD(winref);
+ WRITE_LOCATION_FIELD(location);
+ }
+
+ static void
_outArrayRef(StringInfo str, ArrayRef *node)
{
WRITE_NODE_TYPE("ARRAYREF");
***************
*** 1634,1639 **** _outSelectStmt(StringInfo str, SelectStmt *node)
--- 1671,1677 ----
WRITE_NODE_FIELD(whereClause);
WRITE_NODE_FIELD(groupClause);
WRITE_NODE_FIELD(havingClause);
+ WRITE_NODE_FIELD(windowClause);
WRITE_NODE_FIELD(valuesLists);
WRITE_NODE_FIELD(sortClause);
WRITE_NODE_FIELD(limitOffset);
***************
*** 1655,1660 **** _outFuncCall(StringInfo str, FuncCall *node)
--- 1693,1699 ----
WRITE_BOOL_FIELD(agg_star);
WRITE_BOOL_FIELD(agg_distinct);
WRITE_BOOL_FIELD(func_variadic);
+ WRITE_NODE_FIELD(win_definition);
WRITE_LOCATION_FIELD(location);
}
***************
*** 1779,1784 **** _outQuery(StringInfo str, Query *node)
--- 1818,1824 ----
WRITE_BOOL_FIELD(hasAggs);
WRITE_BOOL_FIELD(hasSubLinks);
WRITE_BOOL_FIELD(hasDistinctOn);
+ WRITE_BOOL_FIELD(hasWindow);
WRITE_NODE_FIELD(rtable);
WRITE_NODE_FIELD(jointree);
WRITE_NODE_FIELD(targetList);
***************
*** 1787,1792 **** _outQuery(StringInfo str, Query *node)
--- 1827,1833 ----
WRITE_NODE_FIELD(havingQual);
WRITE_NODE_FIELD(distinctClause);
WRITE_NODE_FIELD(sortClause);
+ WRITE_NODE_FIELD(windowList);
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
WRITE_NODE_FIELD(rowMarks);
***************
*** 1805,1810 **** _outSortGroupClause(StringInfo str, SortGroupClause *node)
--- 1846,1897 ----
}
static void
+ _outOrderClause(StringInfo str, OrderClause *node)
+ {
+ WRITE_NODE_TYPE("ORDERCLAUSE");
+
+ WRITE_UINT_FIELD(tleSortGroupRef);
+ WRITE_OID_FIELD(eqop);
+ WRITE_OID_FIELD(sortop);
+ WRITE_BOOL_FIELD(nulls_first);
+ }
+
+ static void
+ _outPartitionClause(StringInfo str, PartitionClause *node)
+ {
+ WRITE_NODE_TYPE("PARTITIONCLAUSE");
+
+ WRITE_UINT_FIELD(tleSortGroupRef);
+ WRITE_OID_FIELD(eqop);
+ WRITE_OID_FIELD(sortop);
+ WRITE_BOOL_FIELD(nulls_first);
+ }
+
+ static void
+ _outWinDef(StringInfo str, WinDef *node)
+ {
+ WRITE_NODE_TYPE("WINDEF");
+
+ WRITE_NODE_FIELD(partitionClause);
+ WRITE_NODE_FIELD(orderClause);
+ WRITE_NODE_FIELD(expr_list);
+ WRITE_STRING_FIELD(name);
+ WRITE_LOCATION_FIELD(location);
+ }
+
+ static void
+ _outWindowClause(StringInfo str, WindowClause *node)
+ {
+ WRITE_NODE_TYPE("WINDOWCLAUSE");
+
+ WRITE_NODE_FIELD(partitionClause);
+ WRITE_NODE_FIELD(orderClause);
+ WRITE_STRING_FIELD(name);
+ WRITE_UINT_FIELD(winref);
+ }
+
+
+ static void
_outRowMarkClause(StringInfo str, RowMarkClause *node)
{
WRITE_NODE_TYPE("ROWMARKCLAUSE");
***************
*** 2204,2209 **** _outNode(StringInfo str, void *obj)
--- 2291,2299 ----
case T_Limit:
_outLimit(str, obj);
break;
+ case T_Window:
+ _outWindow(str, obj);
+ break;
case T_Hash:
_outHash(str, obj);
break;
***************
*** 2228,2233 **** _outNode(StringInfo str, void *obj)
--- 2318,2326 ----
case T_Aggref:
_outAggref(str, obj);
break;
+ case T_WinAggref:
+ _outWinAggref(str, obj);
+ break;
case T_ArrayRef:
_outArrayRef(str, obj);
break;
***************
*** 2446,2451 **** _outNode(StringInfo str, void *obj)
--- 2539,2556 ----
case T_SortGroupClause:
_outSortGroupClause(str, obj);
break;
+ case T_OrderClause:
+ _outOrderClause(str, obj);
+ break;
+ case T_PartitionClause:
+ _outPartitionClause(str, obj);
+ break;
+ case T_WinDef:
+ _outWinDef(str, obj);
+ break;
+ case T_WindowClause:
+ _outWindowClause(str, obj);
+ break;
case T_RowMarkClause:
_outRowMarkClause(str, obj);
break;
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 155,160 **** _readQuery(void)
--- 155,161 ----
READ_BOOL_FIELD(hasAggs);
READ_BOOL_FIELD(hasSubLinks);
READ_BOOL_FIELD(hasDistinctOn);
+ READ_BOOL_FIELD(hasWindow);
READ_NODE_FIELD(rtable);
READ_NODE_FIELD(jointree);
READ_NODE_FIELD(targetList);
***************
*** 163,168 **** _readQuery(void)
--- 164,170 ----
READ_NODE_FIELD(havingQual);
READ_NODE_FIELD(distinctClause);
READ_NODE_FIELD(sortClause);
+ READ_NODE_FIELD(windowList);
READ_NODE_FIELD(limitOffset);
READ_NODE_FIELD(limitCount);
READ_NODE_FIELD(rowMarks);
***************
*** 216,221 **** _readSortGroupClause(void)
--- 218,288 ----
}
/*
+ * _readOrderClause
+ */
+ static OrderClause *
+ _readOrderClause(void)
+ {
+ READ_LOCALS(OrderClause);
+
+ READ_UINT_FIELD(tleSortGroupRef);
+ READ_OID_FIELD(eqop);
+ READ_OID_FIELD(sortop);
+ READ_BOOL_FIELD(nulls_first);
+
+ READ_DONE();
+ }
+
+ /*
+ * _readPartitionClause
+ */
+ static PartitionClause *
+ _readPartitionClause(void)
+ {
+ READ_LOCALS(PartitionClause);
+
+ READ_UINT_FIELD(tleSortGroupRef);
+ READ_OID_FIELD(eqop);
+ READ_OID_FIELD(sortop);
+ READ_BOOL_FIELD(nulls_first);
+
+ READ_DONE();
+ }
+
+ /*
+ * _readWinDef
+ */
+ static WinDef *
+ _readWinDef(void)
+ {
+ READ_LOCALS(WinDef);
+
+ READ_NODE_FIELD(partitionClause);
+ READ_NODE_FIELD(orderClause);
+ READ_NODE_FIELD(expr_list);
+ READ_STRING_FIELD(name);
+ READ_LOCATION_FIELD(location);
+
+ READ_DONE();
+ }
+
+ /*
+ * _readWindowClause
+ */
+ static WindowClause *
+ _readWindowClause(void)
+ {
+ READ_LOCALS(WindowClause);
+
+ READ_NODE_FIELD(partitionClause);
+ READ_NODE_FIELD(orderClause);
+ READ_STRING_FIELD(name);
+ READ_UINT_FIELD(winref);
+
+ READ_DONE();
+ }
+
+ /*
* _readRowMarkClause
*/
static RowMarkClause *
***************
*** 377,382 **** _readAggref(void)
--- 444,469 ----
}
/*
+ * _readWinAggref
+ */
+ static WinAggref *
+ _readWinAggref(void)
+ {
+ READ_LOCALS(WinAggref);
+
+ READ_OID_FIELD(aggfnoid);
+ READ_OID_FIELD(aggtype);
+ READ_NODE_FIELD(args);
+ READ_UINT_FIELD(agglevelsup);
+ READ_BOOL_FIELD(aggstar);
+ READ_BOOL_FIELD(aggdistinct);
+ READ_UINT_FIELD(winref);
+ READ_LOCATION_FIELD(location);
+
+ READ_DONE();
+ }
+
+ /*
* _readArrayRef
*/
static ArrayRef *
***************
*** 1057,1062 **** parseNodeString(void)
--- 1144,1157 ----
return_value = _readQuery();
else if (MATCH("SORTGROUPCLAUSE", 15))
return_value = _readSortGroupClause();
+ else if (MATCH("ORDERCLAUSE", 11))
+ return_value = _readOrderClause();
+ else if (MATCH("PARTITIONCLAUSE", 15))
+ return_value = _readPartitionClause();
+ else if (MATCH("WINDEF", 6))
+ return_value = _readWinDef();
+ else if (MATCH("WINDOWCLAUSE", 12))
+ return_value = _readWindowClause();
else if (MATCH("ROWMARKCLAUSE", 13))
return_value = _readRowMarkClause();
else if (MATCH("SETOPERATIONSTMT", 16))
***************
*** 1075,1080 **** parseNodeString(void)
--- 1170,1177 ----
return_value = _readParam();
else if (MATCH("AGGREF", 6))
return_value = _readAggref();
+ else if (MATCH("WINAGGREF", 9))
+ return_value = _readWinAggref();
else if (MATCH("ARRAYREF", 8))
return_value = _readArrayRef();
else if (MATCH("FUNCEXPR", 8))
*** a/src/backend/optimizer/path/allpaths.c
--- b/src/backend/optimizer/path/allpaths.c
***************
*** 825,831 **** standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
*
* Conditions checked here:
*
! * 1. If the subquery has a LIMIT clause, we must not push down any quals,
* since that could change the set of rows returned.
*
* 2. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
--- 825,831 ----
*
* Conditions checked here:
*
! * 1. If the subquery has a LIMIT or Window clause, we must not push down any quals,
* since that could change the set of rows returned.
*
* 2. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
***************
*** 846,852 **** subquery_is_pushdown_safe(Query *subquery, Query *topquery,
SetOperationStmt *topop;
/* Check point 1 */
! if (subquery->limitOffset != NULL || subquery->limitCount != NULL)
return false;
/* Are we at top level, or looking at a setop component? */
--- 846,853 ----
SetOperationStmt *topop;
/* Check point 1 */
! if (subquery->limitOffset != NULL || subquery->limitCount != NULL
! || subquery->hasWindow)
return false;
/* Are we at top level, or looking at a setop component? */
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 3219,3224 **** make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount,
--- 3219,3345 ----
return node;
}
+ /*
+ * make_window
+ * Almost same as make_agg in the meaning of partition columns
+ */
+ Window *
+ make_window(PlannerInfo *root,
+ List *tlist,
+ WindowClause *parse,
+ Oid *prtOperators,
+ Oid *ordOperators,
+ Plan *lefttree)
+ {
+ Window *node = makeNode(Window);
+ Plan *plan = &node->plan;
+ List *sub_tlist = lefttree->targetlist;
+ ListCell *lc;
+ int numCols;
+ int num_winexpr;
+
+ /*
+ * count up window evaluations
+ */
+ num_winexpr = 0;
+ foreach(lc, tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Node *winexpr;
+
+ winexpr = find_winexpr(tle);
+ if(winexpr)
+ num_winexpr++;
+ }
+
+ copy_plan_costsize(plan, lefttree);
+
+ /*
+ * Charge one cpu_operator_cost per comparison per input tuple. We assume
+ * all columns get compared at most of the tuples.
+ */
+ numCols = list_length(tlist);
+ plan->total_cost += cpu_operator_cost * lefttree->plan_rows * numCols;
+ plan->total_cost += cpu_operator_cost * num_winexpr * lefttree->plan_rows;
+
+ plan->lefttree = lefttree;
+ plan->targetlist = tlist;
+ plan->qual = NIL;
+
+ numCols = list_length(parse->partitionClause);
+ node->prtNumCols = numCols;
+ if (parse->partitionClause)
+ {
+ int keyno = 0;
+ AttrNumber *prtColIdx = NULL;
+ ListCell *pl;
+
+ prtColIdx = (AttrNumber *) palloc(sizeof(AttrNumber) * numCols);
+
+ foreach(pl, parse->partitionClause)
+ {
+ PartitionClause *prtcl = (PartitionClause *) lfirst(pl);
+ Node *prtexpr = get_sortgroupclause_expr(prtcl, sub_tlist);
+ TargetEntry *te = NULL;
+ ListCell *l;
+
+ foreach(l, sub_tlist)
+ {
+ te = (TargetEntry *) lfirst(l);
+ if (equal(prtexpr, te->expr))
+ break;
+ }
+
+ Assert(te);
+ prtColIdx[keyno++] = te->resno;
+ }
+ node->prtColIdx = prtColIdx;
+ node->prtOperators = prtOperators;
+ }
+
+ numCols = list_length(parse->orderClause);
+ node->ordNumCols = numCols;
+ if (parse->orderClause)
+ {
+ int keyno = 0;
+ AttrNumber *ordColIdx = NULL;
+ ListCell *ol;
+
+ ordColIdx = (AttrNumber *) palloc(sizeof(AttrNumber) * numCols);
+ foreach(ol, parse->orderClause)
+ {
+ OrderClause *ordcl = (OrderClause *) lfirst(ol);
+ Node *ordexpr = get_sortgroupclause_expr(ordcl, sub_tlist);
+ TargetEntry *te = NULL;
+ ListCell *l;
+
+ foreach(l, sub_tlist)
+ {
+ te = (TargetEntry *) lfirst(l);
+ if (equal(ordexpr, te->expr))
+ break;
+ }
+
+ Assert(te);
+ ordColIdx[keyno++] = te->resno;
+ }
+ /* orderClause columns will be used as "key columns". */
+ node->ordColIdx = ordColIdx;
+ node->ordOperators = ordOperators;
+ }
+
+ /*
+ * Currently, the parser doesn't accept frame clause.
+ * This is for future implementation.
+ */
+ node->frameType = WINDOW_FRAME_NONE;
+ node->preceding = NULL;
+ node->following = NULL;
+
+ node->winref = parse->winref;
+
+ return node;
+ }
/*
* make_result
*** a/src/backend/optimizer/plan/planagg.c
--- b/src/backend/optimizer/plan/planagg.c
***************
*** 52,57 **** static ScanDirection match_agg_to_index_col(MinMaxAggInfo *info,
--- 52,58 ----
static void make_agg_subplan(PlannerInfo *root, MinMaxAggInfo *info);
static Node *replace_aggs_with_params_mutator(Node *node, List **context);
static Oid fetch_agg_sort_op(Oid aggfnoid);
+ static bool find_aggref_walker(Node *node, Aggref **context);
/*
***************
*** 625,627 **** fetch_agg_sort_op(Oid aggfnoid)
--- 626,652 ----
return aggsortop;
}
+
+ Aggref *
+ find_aggref(Node *node)
+ {
+ Aggref *context = NULL;
+
+ find_aggref_walker(node, &context);
+ return context;
+ }
+
+ static bool
+ find_aggref_walker(Node *node, Aggref **context)
+ {
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, Aggref))
+ {
+ *context = (Aggref *) node;
+ return true;
+ }
+
+ return expression_tree_walker(node, find_aggref_walker, (void *) context);
+ }
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
***************
*** 22,27 ****
--- 22,28 ----
#include "executor/nodeAgg.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
+ #include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/pathnode.h"
***************
*** 82,87 **** static void locate_grouping_columns(PlannerInfo *root,
--- 83,90 ----
List *sub_tlist,
AttrNumber *groupColIdx);
static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
+ static List *preprocess_window(List *tlist, Plan *subplan);
+ static List *window_tlist(List *tlist, Index winref, bool *has_win);
/*****************************************************************************
***************
*** 1206,1211 **** grouping_planner(PlannerInfo *root, double tuple_fraction)
--- 1209,1285 ----
} /* end of if (setOperations) */
/*
+ * Window nodes are stacked one by one for each window because Window
+ * functions are evaluated in the appropriate window. Hence, in a window
+ * level, upper window expressions are replaced by nulls so as to be
+ * evaluated in the upper Window node. For lower expressions, setrefs
+ * will replace them to Var nodes.
+ */
+ if (parse->windowList)
+ {
+ ListCell *l;
+ List *window_pathkeys = NIL;
+
+ result_plan->targetlist = preprocess_window(tlist, result_plan);
+ foreach(l, parse->windowList)
+ {
+ List *current_tlist;
+ List *partition_pathkeys = NIL;
+ List *order_pathkeys = NIL;
+ WindowClause *wc = (WindowClause *) lfirst(l);
+ bool has_win = false;
+
+ window_pathkeys = NIL;
+ current_tlist = window_tlist(tlist, wc->winref, &has_win);
+ if (!has_win)
+ continue;
+
+ /*
+ * Currently, Window Partitioning strategy is only by Sort.
+ * So just join partitionClause and orderClause
+ * to match Grouping. Hashing algorithm will be considered later.
+ */
+ if (wc->partitionClause)
+ {
+ partition_pathkeys = make_pathkeys_for_sortclauses(root,
+ wc->partitionClause,
+ result_plan->targetlist,
+ false);
+ }
+
+ if (wc->orderClause)
+ {
+ order_pathkeys = make_pathkeys_for_sortclauses(root,
+ wc->orderClause,
+ result_plan->targetlist,
+ false);
+ }
+
+ Assert(partition_pathkeys != NIL || order_pathkeys != NIL);
+ /*
+ * create Sort node under Window, so PARTITION BY works
+ */
+ window_pathkeys = list_concat(partition_pathkeys, order_pathkeys);
+ if (!pathkeys_contained_in(window_pathkeys, current_pathkeys))
+ {
+ result_plan = (Plan *) make_sort_from_pathkeys(root,
+ result_plan,
+ window_pathkeys,
+ -1);
+ current_pathkeys = window_pathkeys;
+ }
+
+ result_plan = (Plan *) make_window(root,
+ current_tlist,
+ wc,
+ extract_grouping_ops(wc->partitionClause),
+ extract_grouping_ops(wc->orderClause),
+ result_plan);
+ }
+ current_pathkeys = NIL;
+ }
+
+ /*
* If there is a DISTINCT clause, add the necessary node(s).
*/
if (parse->distinctClause)
***************
*** 2008,2014 **** make_subplanTargetList(PlannerInfo *root,
* If we're not grouping or aggregating, there's nothing to do here;
* query_planner should receive the unmodified target list.
*/
! if (!parse->hasAggs && !parse->groupClause && !root->hasHavingQual)
{
*need_tlist_eval = true;
return tlist;
--- 2082,2088 ----
* If we're not grouping or aggregating, there's nothing to do here;
* query_planner should receive the unmodified target list.
*/
! if (!parse->hasAggs && !parse->groupClause && !root->hasHavingQual && !parse->hasWindow)
{
*need_tlist_eval = true;
return tlist;
***************
*** 2168,2170 **** postprocess_setop_tlist(List *new_tlist, List *orig_tlist)
--- 2242,2449 ----
elog(ERROR, "resjunk output columns are not implemented");
return new_tlist;
}
+
+ /*
+ * preprocess_window -
+ * given parser tlist, returns recomposed tlist for current top plan.
+ *
+ * Before create Window nodes, window expressions are removed from current
+ * tlist because current plan must not be a Window node. These expressions
+ * are evaluated in appropriate window later.
+ *
+ * There are two main cases to be considered.
+ * 1. Agg/Group
+ * Vars from scan node required by any of expression should have been pulled
+ * up to now. So there's no need to consider it. A Aggref node which is contained
+ * in window expression is pulled and appended at the tail for upper Window node use.
+ *
+ * 2. Other Scan
+ * The situation resembles to the one in Agg/Group. Var expressions are pulled
+ * (tlist is flattened), and other evaluation expressions but window expression
+ * are as well, since in Window nodes we take care of only window expression.
+ *
+ */
+ static List *
+ preprocess_window(List *tlist, Plan *subplan)
+ {
+ List *output_targetlist = NIL;
+ ListCell *l;
+ AttrNumber resno;
+
+ /*
+ * Agg/Group nodes have pushed vars down to lower scan node.
+ * So we don't take care of them. Those nodes may be found in
+ * window expression nodes which will be evaluated later than aggs.
+ * We need pull pure Aggref node and append current tlist, so
+ * that Agg node doesn't care about window expressions.
+ */
+ if (IsA(subplan, Agg) || IsA(subplan, Group))
+ {
+ List *pulled_aggrefs = NIL;
+ Aggref *aggref;
+ AttrNumber tlist_resno;
+
+ foreach(l, subplan->targetlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(l);
+
+ if (find_winexpr(tle))
+ {
+ /* pull pure aggref in window expressions */
+ aggref = find_aggref((Node *) tle->expr);
+ if (aggref)
+ {
+ tle = flatCopyTargetEntry(tle);
+ tle->expr = (Expr *) makeNullConst(exprType((Node *) tle->expr),
+ exprTypmod((Node *) tle->expr));
+
+ pulled_aggrefs = lappend(pulled_aggrefs, aggref);
+ }
+ }
+ /* otherwise, it is safe to be evaluated in Agg node */
+ output_targetlist = lappend(output_targetlist, tle);
+ }
+
+ /*
+ * Here pulled pure Aggref nodes are appended to tlist.
+ * Through these process, window expressions are removed.
+ */
+ resno = list_length(output_targetlist);
+ tlist_resno = list_length(tlist);
+ foreach(l, pulled_aggrefs)
+ {
+ TargetEntry *tle;
+ aggref = (Aggref *) lfirst(l);
+
+ resno++;
+ tle = makeTargetEntry((Expr *) aggref,
+ resno,
+ NULL,
+ true);
+ output_targetlist = lappend(output_targetlist, tle);
+
+ /*
+ * So as keep Aggref result until the topmost window,
+ * add the pure aggref entry to tlist.
+ * This node will be transfered to Var in setrefs.
+ */
+ tlist_resno++;
+ tle = makeTargetEntry((Expr *) aggref,
+ tlist_resno,
+ NULL,
+ true);
+ tlist = lappend(tlist, tle);
+ }
+ }
+ else
+ {
+ /*
+ * copyObject() is required, as in tlist = lappend(tlist, tle);
+ * Without this, it may fall into a infinte loop.
+ */
+ output_targetlist = copyObject(flatten_tlist(tlist));
+ resno = list_length(tlist);
+
+ foreach(l, output_targetlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(l);
+ Var *var = (Var *) tle->expr;
+ if (!tlist_member((Node *)var, tlist))
+ {
+ resno++;
+ tle = makeTargetEntry(copyObject(var),
+ resno,
+ NULL,
+ true);
+ tlist = lappend(tlist, tle);
+ }
+ }
+
+ resno = list_length(output_targetlist);
+ foreach(l, tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(l);
+ Node *winexpr;
+ ListCell *lc;
+
+ /*
+ * TargetEntry that contains window expression is ignored here,
+ * added later in window_tlist().
+ */
+ winexpr = find_winexpr(tle);
+ if (winexpr)
+ continue;
+
+ foreach(lc, output_targetlist)
+ {
+ TargetEntry *subtle = (TargetEntry *) lfirst(lc);
+ if (equal(tle->expr, subtle->expr))
+ {
+ /*
+ * It is necessary since flatten_tlist() doesn't pull
+ * their resosortgroupref.
+ */
+ subtle->ressortgroupref = tle->ressortgroupref;
+ break;
+ }
+ }
+
+ /*
+ * If an entry isn't in flatten tlist nor window expression,
+ * it must be evaluated here before any of Window nodes.
+ */
+ if (!lc)
+ {
+ TargetEntry *newtle = flatCopyTargetEntry(tle);
+
+ resno++;
+ newtle->resno = resno;
+ output_targetlist = lappend(output_targetlist, newtle);
+ }
+ }
+ }
+
+ return output_targetlist;
+ }
+
+ /*
+ * window_tlist -
+ *
+ * creates tlist suitable for current window, indicated by winref.
+ * For the upper window expressions than current, they are relpaced
+ * by NullConst, so that setrefs can understand where the references
+ * may stop.
+ * With window clause syntax, there may be a window in which none of
+ * window evaluations is executed. In this case, we can pass by the window.
+ */
+ static List *
+ window_tlist(List *tlist, Index winref, bool *has_win)
+ {
+ List *output_targetlist = NIL;
+ ListCell *l;
+
+ foreach(l, tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(l);
+ WinAggref *winexpr;
+
+ tle = flatCopyTargetEntry(tle);
+ winexpr = (WinAggref *) find_winexpr_greater(tle);
+ if (winexpr && winref == winexpr->winref)
+ *has_win = true;
+
+ /*
+ * window that contains evaluation on upper than current window is set null.
+ * for the lower ones, setrefs will fix them to Vars pointing to OUTER.
+ */
+ if (winexpr && winref < winexpr->winref)
+ {
+ tle->expr = (Expr *) makeNullConst(exprType((Node *) tle->expr),
+ exprTypmod((Node *) tle->expr));
+ }
+
+ output_targetlist = lappend(output_targetlist, tle);
+ }
+
+ return output_targetlist;
+ }
*** a/src/backend/optimizer/plan/setrefs.c
--- b/src/backend/optimizer/plan/setrefs.c
***************
*** 385,390 **** set_plan_refs(PlannerGlobal *glob, Plan *plan, int rtoffset)
--- 385,391 ----
break;
case T_Agg:
case T_Group:
+ case T_Window:
set_upper_references(glob, plan, rtoffset);
break;
case T_Result:
*** a/src/backend/optimizer/plan/subselect.c
--- b/src/backend/optimizer/plan/subselect.c
***************
*** 1798,1803 **** finalize_plan(PlannerInfo *root, Plan *plan, Bitmapset *valid_params)
--- 1798,1804 ----
case T_Unique:
case T_SetOp:
case T_Group:
+ case T_Window:
break;
default:
*** a/src/backend/optimizer/prep/prepjointree.c
--- b/src/backend/optimizer/prep/prepjointree.c
***************
*** 920,925 **** is_simple_subquery(Query *subquery)
--- 920,926 ----
* limiting.
*/
if (subquery->hasAggs ||
+ subquery->hasWindow ||
subquery->groupClause ||
subquery->havingQual ||
subquery->sortClause ||
*** a/src/backend/optimizer/util/clauses.c
--- b/src/backend/optimizer/util/clauses.c
***************
*** 778,783 **** contain_volatile_functions_walker(Node *node, void *context)
--- 778,790 ----
return true;
/* else fall through to check args */
}
+ else if (IsA(node, WinAggref))
+ {
+ WinAggref *winagg = (WinAggref *) node;
+
+ if (func_volatile(winagg->aggfnoid) == PROVOLATILE_VOLATILE)
+ return true;
+ }
else if (IsA(node, OpExpr))
{
OpExpr *expr = (OpExpr *) node;
*** a/src/backend/optimizer/util/tlist.c
--- b/src/backend/optimizer/util/tlist.c
***************
*** 366,368 **** grouping_is_hashable(List *groupClause)
--- 366,369 ----
}
return true;
}
+
*** a/src/backend/optimizer/util/var.c
--- b/src/backend/optimizer/util/var.c
***************
*** 53,58 **** typedef struct
--- 53,64 ----
int sublevels_up;
} flatten_join_alias_vars_context;
+ typedef struct
+ {
+ Node *node;
+ int prefer;
+ } find_winexpr_context;
+
static bool pull_varnos_walker(Node *node,
pull_varnos_context *context);
static bool pull_varattnos_walker(Node *node, Bitmapset **varattnos);
***************
*** 68,73 **** static bool pull_var_clause_walker(Node *node,
--- 74,81 ----
static Node *flatten_join_alias_vars_mutator(Node *node,
flatten_join_alias_vars_context *context);
static Relids alias_relid_set(PlannerInfo *root, Relids relids);
+ static Node *find_winexpr_inner(TargetEntry *tle, int prefer);
+ static bool find_winexpr_walker(Node *node, find_winexpr_context *context);
/*
***************
*** 708,710 **** alias_relid_set(PlannerInfo *root, Relids relids)
--- 716,788 ----
bms_free(tmprelids);
return result;
}
+
+ Node *
+ find_winexpr_greater(TargetEntry *tle)
+ {
+ return find_winexpr_inner(tle, 1);
+ }
+
+ Node *
+ find_winexpr_lesser(TargetEntry *tle)
+ {
+ return find_winexpr_inner(tle, -1);
+ }
+
+ Node *
+ find_winexpr(TargetEntry *tle)
+ {
+ return find_winexpr_inner(tle, 0);
+ }
+
+ /*
+ * find_winexpr -
+ * find window evaluation node in the given TargetEntry.
+ * parameter prefer means caller prefers greater winref in > 0 and
+ * lesser winref in < 0. prefer 0 means no care.
+ */
+ static Node *
+ find_winexpr_inner(TargetEntry *tle, int prefer)
+ {
+ find_winexpr_context context;
+
+ context.node = NULL;
+ context.prefer = prefer;
+ find_winexpr_walker((Node *) tle->expr, &context);
+
+ return context.node;
+ }
+
+ /*
+ * find_winexpr_walker -
+ */
+ static bool
+ find_winexpr_walker(Node *node, find_winexpr_context *context)
+ {
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, WinAggref))
+ {
+ if (context->node)
+ {
+ if (context->prefer > 0)
+ {
+ if (((WinAggref *) context->node)->winref < ((WinAggref *) node)->winref)
+ context->node = node;
+ }
+ else /* context->node != NULL && context->prefer == 0 doesn't make sanse */
+ {
+ if (((WinAggref *) context->node)->winref > ((WinAggref *) node)->winref)
+ context->node = node;
+ }
+ }
+ else
+ context->node = node;
+
+ if (context->prefer == 0)
+ return true;
+ }
+
+ return expression_tree_walker(node, find_winexpr_walker, (void *) context);
+ }
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 746,752 **** transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
qry->hasDistinctOn = true;
}
! /* transform LIMIT */
qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset,
"OFFSET");
qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
--- 746,756 ----
qry->hasDistinctOn = true;
}
! pstate->p_windef_list = list_concat(stmt->windowClause, pstate->p_windef_list);
! qry->windowList = transformWinDef(pstate,
! pstate->p_windef_list,
! &qry->targetList);
!
qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset,
"OFFSET");
qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
***************
*** 765,770 **** transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
--- 769,775 ----
qry->hasSubLinks = pstate->p_hasSubLinks;
qry->hasAggs = pstate->p_hasAggs;
+ qry->hasWindow = pstate->p_hasWindow;
if (pstate->p_hasAggs || qry->groupClause || qry->havingQual)
parseCheckAggregates(pstate, qry);
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 364,369 **** static TypeName *TableFuncTypeName(List *columns);
--- 364,371 ----
%type document_or_content
%type xml_whitespace_option
+ %type partition_clause opt_partition_clause window_clause window_definition_list
+ %type window_definition window_specification over_clause
/*
* If you make any token changes, update the keyword table in
***************
*** 422,430 **** static TypeName *TableFuncTypeName(List *columns);
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
! ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
! PARSER PARTIAL PASSWORD PLACING PLANS POSITION
PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE
--- 424,432 ----
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
! ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
! PARSER PARTIAL PARTITION PASSWORD PLACING PLANS POSITION
PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE
***************
*** 450,456 **** static TypeName *TableFuncTypeName(List *columns);
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
VERBOSE VERSION_P VIEW VOLATILE
! WHEN WHERE WHITESPACE_P WITH WITHOUT WORK WRITE
XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLFOREST XMLPARSE
XMLPI XMLROOT XMLSERIALIZE
--- 452,458 ----
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
VERBOSE VERSION_P VIEW VOLATILE
! WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRITE
XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLFOREST XMLPARSE
XMLPI XMLROOT XMLSERIALIZE
***************
*** 6292,6298 **** select_clause:
simple_select:
SELECT opt_distinct target_list
into_clause from_clause where_clause
! group_clause having_clause
{
SelectStmt *n = makeNode(SelectStmt);
n->distinctClause = $2;
--- 6294,6300 ----
simple_select:
SELECT opt_distinct target_list
into_clause from_clause where_clause
! group_clause having_clause window_clause
{
SelectStmt *n = makeNode(SelectStmt);
n->distinctClause = $2;
***************
*** 6302,6307 **** simple_select:
--- 6304,6310 ----
n->whereClause = $6;
n->groupClause = $7;
n->havingClause = $8;
+ n->windowClause = $9;
$$ = (Node *)n;
}
| values_clause { $$ = $1; }
***************
*** 7920,7926 **** 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 '(' ')'
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 7923,7929 ----
* (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;
***************
*** 7928,7937 **** func_expr: func_name '(' ')'
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' expr_list ')'
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 7931,7941 ----
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
+ n->win_definition = (WinDef *) $4;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' expr_list ')' over_clause
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
***************
*** 7939,7948 **** func_expr: func_name '(' ')'
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' VARIADIC a_expr ')'
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 7943,7953 ----
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
+ n->win_definition = (WinDef *) $5;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' VARIADIC a_expr ')' /* intentionally not accept over_clause */
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
***************
*** 7950,7959 **** func_expr: func_name '(' ')'
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = TRUE;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' expr_list ',' VARIADIC a_expr ')'
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 7955,7965 ----
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = TRUE;
+ n->win_definition = NULL;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' expr_list ',' VARIADIC a_expr ')' /* intentionally not accept over_clause */
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
***************
*** 7961,7970 **** func_expr: func_name '(' ')'
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = TRUE;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' ALL expr_list ')'
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 7967,7977 ----
n->agg_star = FALSE;
n->agg_distinct = FALSE;
n->func_variadic = TRUE;
+ n->win_definition = NULL;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' ALL expr_list ')' /* intentionally not accept over_clause */
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
***************
*** 7976,7985 **** func_expr: func_name '(' ')'
* for that in FuncCall at the moment.
*/
n->func_variadic = FALSE;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' DISTINCT expr_list ')'
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
--- 7983,7993 ----
* for that in FuncCall at the moment.
*/
n->func_variadic = FALSE;
+ n->win_definition = NULL;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' DISTINCT expr_list ')' /* intentionally not accept over_clause */
{
FuncCall *n = makeNode(FuncCall);
n->funcname = $1;
***************
*** 7987,7996 **** func_expr: func_name '(' ')'
n->agg_star = FALSE;
n->agg_distinct = TRUE;
n->func_variadic = FALSE;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' '*' ')'
{
/*
* We consider AGGREGATE(*) to invoke a parameterless
--- 7995,8005 ----
n->agg_star = FALSE;
n->agg_distinct = TRUE;
n->func_variadic = FALSE;
+ n->win_definition = NULL;
n->location = @1;
$$ = (Node *)n;
}
! | func_name '(' '*' ')' over_clause
{
/*
* We consider AGGREGATE(*) to invoke a parameterless
***************
*** 8008,8013 **** func_expr: func_name '(' ')'
--- 8017,8023 ----
n->agg_star = TRUE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
+ n->win_definition = (WinDef *) $5;
n->location = @1;
$$ = (Node *)n;
}
***************
*** 8433,8438 **** xml_whitespace_option: PRESERVE WHITESPACE_P { $$ = TRUE; }
--- 8443,8519 ----
;
/*
+ * Window Definitions
+ *
+ * In SQL2003 window may appear after a function call or after HAVING, with the same syntax.
+ * If there is window syntax after HAVING, some of the windows can refer to it.
+ */
+ over_clause: OVER window_specification { $$ = $2; }
+ | OVER IDENT
+ {
+ WinDef *n = makeNode(WinDef);
+ n->partitionClause = NIL;
+ n->orderClause = NIL;
+ n->expr_list = NIL;
+ n->name = pstrdup($2);
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+ window_specification: '(' opt_partition_clause opt_sort_clause ')'
+ {
+ WinDef *n = makeNode(WinDef);
+ n->partitionClause = $2;
+ n->orderClause = $3;
+ n->expr_list = NIL;
+ n->name = NULL;
+ n->location = @1;
+ if (!n->partitionClause && !n->orderClause)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("either PARTITION BY or ORDER BY must be specified in window clause")));
+ }
+ $$ = (Node *) n;
+ }
+ ;
+
+ opt_partition_clause:
+ partition_clause { $$ = $1; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
+ partition_clause: PARTITION BY expr_list
+ {
+ $$ = $3;
+ }
+ ;
+
+ window_clause:
+ WINDOW window_definition_list { $$ = $2; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+ window_definition_list:
+ window_definition
+ {
+ $$ = list_make1($1);
+ }
+ | window_definition_list ',' window_definition
+ {
+ $$ = lappend($1, $3);
+ }
+ ;
+ window_definition:
+ IDENT AS window_specification
+ {
+ WinDef *n = (WinDef *) $3;
+ n->name = pstrdup($1);
+ $$ = (Node *) n;
+ }
+
+
+ /*
* Supporting nonterminals for expressions.
*/
*** a/src/backend/parser/keywords.c
--- b/src/backend/parser/keywords.c
***************
*** 280,291 **** const ScanKeyword ScanKeywords[] = {
--- 280,293 ----
{"order", ORDER, RESERVED_KEYWORD},
{"out", OUT_P, COL_NAME_KEYWORD},
{"outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD},
+ {"over", OVER, RESERVED_KEYWORD},
{"overlaps", OVERLAPS, TYPE_FUNC_NAME_KEYWORD},
{"overlay", OVERLAY, COL_NAME_KEYWORD},
{"owned", OWNED, UNRESERVED_KEYWORD},
{"owner", OWNER, UNRESERVED_KEYWORD},
{"parser", PARSER, UNRESERVED_KEYWORD},
{"partial", PARTIAL, UNRESERVED_KEYWORD},
+ {"partition", PARTITION, RESERVED_KEYWORD},
{"password", PASSWORD, UNRESERVED_KEYWORD},
{"placing", PLACING, RESERVED_KEYWORD},
{"plans", PLANS, UNRESERVED_KEYWORD},
***************
*** 402,407 **** const ScanKeyword ScanKeywords[] = {
--- 404,410 ----
{"when", WHEN, RESERVED_KEYWORD},
{"where", WHERE, RESERVED_KEYWORD},
{"whitespace", WHITESPACE_P, UNRESERVED_KEYWORD},
+ {"window", WINDOW, RESERVED_KEYWORD},
/*
* XXX we mark WITH as reserved to force it to be quoted in dumps, even
*** a/src/backend/parser/parse_agg.c
--- b/src/backend/parser/parse_agg.c
***************
*** 67,73 **** transformAggregateCall(ParseState *pstate, Aggref *agg)
*/
if (min_varlevel == 0)
{
! if (checkExprHasAggs((Node *) agg->args))
ereport(ERROR,
(errcode(ERRCODE_GROUPING_ERROR),
errmsg("aggregate function calls cannot be nested")));
--- 67,73 ----
*/
if (min_varlevel == 0)
{
! if (checkExprHasAggs((Node *) agg->args) || checkExprHasWinAggs((Node *) agg->args))
ereport(ERROR,
(errcode(ERRCODE_GROUPING_ERROR),
errmsg("aggregate function calls cannot be nested")));
***************
*** 83,88 **** transformAggregateCall(ParseState *pstate, Aggref *agg)
--- 83,121 ----
pstate->p_hasAggs = true;
}
+ /*
+ * transformWinAggregateCall -
+ *
+ */
+ void
+ transformWinAggregateCall(ParseState *pstate, WinAggref *agg)
+ {
+ int min_varlevel;
+
+ /*
+ * The aggregate's level is the same as the level of the lowest-level
+ * variable or aggregate in its arguments; or if it contains no variables
+ * at all, we presume it to be local.
+ */
+ min_varlevel = find_minimum_var_level((Node *) agg->args);
+
+ /*
+ * An aggregate can't directly contain another aggregate call of the same
+ * level (though outer aggs are okay). We can skip this check if we
+ * didn't find any local vars or aggs.
+ */
+ if (min_varlevel == 0)
+ {
+ if (checkExprHasWinAggs((Node *) agg->args))
+ ereport(ERROR,
+ (errcode(ERRCODE_GROUPING_ERROR),
+ errmsg("aggregate function calls cannot be nested")));
+ }
+
+ if (min_varlevel < 0)
+ min_varlevel = 0;
+ agg->agglevelsup = min_varlevel;
+ }
/*
* parseCheckAggregates
*** a/src/backend/parser/parse_clause.c
--- b/src/backend/parser/parse_clause.c
***************
*** 40,47 ****
#define ORDER_CLAUSE 0
#define GROUP_CLAUSE 1
#define DISTINCT_ON_CLAUSE 2
! static char *clauseText[] = {"ORDER BY", "GROUP BY", "DISTINCT ON"};
static void extractRemainingColumns(List *common_colnames,
List *src_colnames, List *src_colvars,
--- 40,49 ----
#define ORDER_CLAUSE 0
#define GROUP_CLAUSE 1
#define DISTINCT_ON_CLAUSE 2
+ #define OVER_CLAUSE 3
+ #define PARTITION_CLAUSE 4
! static char *clauseText[] = {"ORDER BY", "GROUP BY", "DISTINCT ON", "OVER", "PARTITION"};
static void extractRemainingColumns(List *common_colnames,
List *src_colnames, List *src_colvars,
***************
*** 66,71 **** static Node *buildMergedJoinVar(ParseState *pstate, JoinType jointype,
--- 68,75 ----
Var *l_colvar, Var *r_colvar);
static TargetEntry *findTargetlistEntry(ParseState *pstate, Node *node,
List **tlist, int clause);
+ static WindowClause *findWindowClause(List *wclist, const char *name,
+ List *partitionClause, List *orderClause);
static List *addTargetToSortList(ParseState *pstate, TargetEntry *tle,
List *sortlist, List *targetlist,
SortByDir sortby_dir, SortByNulls sortby_nulls,
***************
*** 1291,1296 **** findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause)
--- 1295,1312 ----
}
/*
+ * in OVER (), findTargetlistEntry must find TargetEntry which is
+ * already transformed.
+ */
+ if(clause == OVER_CLAUSE)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("%s is not in select list",
+ clauseText[clause])));
+ }
+
+ /*
* If no matches, construct a new target entry which is appended to the
* end of the target list. This target is given resjunk = TRUE so that it
* will not be projected into the final tuple.
***************
*** 1406,1411 **** transformSortClause(ParseState *pstate,
--- 1422,1576 ----
}
/*
+ * transformOrderClause -
+ *
+ * OrderClause is a set of SortBys. Only tag type is different.
+ */
+ List *
+ transformOrderClause(ParseState *pstate,
+ List *orderlist,
+ List **targetlist,
+ bool resolveUnknown)
+ {
+ List *result = NIL;
+ ListCell *l;
+ TargetEntry *tle;
+
+ foreach(l, orderlist)
+ {
+ SortBy *sortby = lfirst(l);
+
+ tle = findTargetlistEntry(pstate, sortby->node,
+ targetlist, ORDER_CLAUSE);
+ result = addTargetToSortList(pstate, tle,
+ result, *targetlist,
+ sortby->sortby_dir,
+ sortby->sortby_nulls,
+ sortby->useOp,
+ resolveUnknown);
+ }
+
+ return result;
+ }
+
+ /*
+ * transformPartitionClause -
+ *
+ * Almost everything PartitionClause has is the same as GroupClause.
+ */
+ List *
+ transformPartitionClause(ParseState *pstate,
+ List *partitionlist,
+ List **targetlist)
+ {
+ List *result = NIL;
+ ListCell *l;
+ TargetEntry *tle;
+
+ foreach(l, partitionlist)
+ {
+ Oid restype;
+ Oid sortop;
+ Oid eqop;
+ PartitionClause *pc;
+
+ tle = findTargetlistEntry(pstate, lfirst(l), targetlist, PARTITION_CLAUSE);
+
+ restype = exprType((Node *) tle->expr);
+
+ if (restype == UNKNOWNOID)
+ tle->expr = (Expr *) coerce_type(pstate, (Node *) tle->expr,
+ restype, TEXTOID, -1,
+ COERCION_IMPLICIT,
+ COERCE_IMPLICIT_CAST,
+ -1);
+ pc = makeNode(PartitionClause);
+ pc->tleSortGroupRef = assignSortGroupRef(tle, *targetlist);
+ get_sort_group_operators(restype,
+ false, true, false,
+ &sortop, &eqop, NULL);
+ pc->eqop = eqop;
+ pc->sortop = sortop;
+ pc->nulls_first = false;
+ result = lappend(result, pc);
+ }
+
+ return result;
+ }
+
+ /*
+ * transformWinDef -
+ *
+ */
+ List *
+ transformWinDef(ParseState *pstate,
+ List *windefinition,
+ List **targetlist)
+ {
+ List *result = NIL;
+ ListCell *l;
+ Index winref = 1;
+
+ foreach(l, windefinition)
+ {
+ WinDef *windef = (WinDef *) lfirst(l);
+ List *partitionClause = NIL;
+ List *orderClause = NIL;
+ WindowClause *wc;
+ ListCell *lc;
+
+ partitionClause = transformPartitionClause(pstate,
+ windef->partitionClause,
+ targetlist);
+ orderClause = transformOrderClause(pstate,
+ windef->orderClause,
+ targetlist,
+ true);
+
+ /*
+ * If there is the same node that has been in the list,
+ * refer to it.
+ */
+ wc = findWindowClause(result, windef->name, partitionClause, orderClause);
+ if (!wc)
+ {
+ if (windef->name && windef->expr_list)
+ {
+ /*
+ * Even though OVER clause uses window name, there's
+ * no definition in WINDOW clause.
+ */
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("window name(%s) is not found in WINDOW clause",
+ windef->name)));
+ }
+
+ wc = makeNode(WindowClause);
+ wc->partitionClause = partitionClause;
+ wc->orderClause = orderClause;
+ wc->name = windef->name;
+ wc->winref = winref++;
+
+ result = lappend(result, wc);
+ pstate->p_hasWindow = true;
+ }
+
+ foreach(lc, windef->expr_list)
+ {
+ Node *node = (Node *) lfirst(lc);
+
+ if (IsA(node, WinAggref))
+ {
+ ((WinAggref *) node)->winref = wc->winref;
+ }
+ }
+ }
+
+ return result;
+ }
+
+ /*
* transformDistinctClause -
* transform a DISTINCT clause
*
***************
*** 1807,1809 **** targetIsInSortList(TargetEntry *tle, Oid sortop, List *sortList)
--- 1972,2002 ----
}
return false;
}
+
+ /*
+ * find_window_clause -
+ *
+ * search for the WindowClause which has already been in the list.
+ * It is done by name or by the set of partition and order.
+ */
+ static WindowClause *
+ findWindowClause(List *wclist, const char *name, List *partitionClause, List *orderClause)
+ {
+ ListCell *l;
+
+ foreach(l, wclist)
+ {
+ WindowClause *wc = (WindowClause *) lfirst(l);
+ if (wc->name && name)
+ {
+ if (strcmp(wc->name, name) == 0)
+ return wc;
+ }
+
+ if (equal(wc->partitionClause, partitionClause) &&
+ equal(wc->orderClause, orderClause))
+ return wc;
+ }
+
+ return NULL;
+ }
*** a/src/backend/parser/parse_expr.c
--- b/src/backend/parser/parse_expr.c
***************
*** 353,359 **** transformIndirection(ParseState *pstate, Node *basenode, List *indirection)
list_make1(n),
list_make1(result),
false, false, false,
! true, -1);
}
}
/* process trailing subscripts, if any */
--- 353,359 ----
list_make1(n),
list_make1(result),
false, false, false,
! true, NULL, -1);
}
}
/* process trailing subscripts, if any */
***************
*** 484,490 **** transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(name2)),
list_make1(node),
false, false, false,
! true, cref->location);
}
break;
}
--- 484,490 ----
list_make1(makeString(name2)),
list_make1(node),
false, false, false,
! true, NULL, cref->location);
}
break;
}
***************
*** 514,520 **** transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(name3)),
list_make1(node),
false, false, false,
! true, cref->location);
}
break;
}
--- 514,520 ----
list_make1(makeString(name3)),
list_make1(node),
false, false, false,
! true, NULL, cref->location);
}
break;
}
***************
*** 555,561 **** transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(name4)),
list_make1(node),
false, false, false,
! true, cref->location);
}
break;
}
--- 555,561 ----
list_make1(makeString(name4)),
list_make1(node),
false, false, false,
! true, NULL, cref->location);
}
break;
}
***************
*** 1050,1055 **** transformFuncCall(ParseState *pstate, FuncCall *fn)
--- 1050,1056 ----
fn->agg_distinct,
fn->func_variadic,
false,
+ fn->win_definition,
fn->location);
}
*** a/src/backend/parser/parse_func.c
--- b/src/backend/parser/parse_func.c
***************
*** 15,20 ****
--- 15,21 ----
#include "postgres.h"
#include "access/heapam.h"
+ #include "catalog/pg_aggregate.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
***************
*** 39,44 **** static Node *ParseComplexProjection(ParseState *pstate, char *funcname,
--- 40,46 ----
Node *first_arg, int location);
static void unknown_attribute(ParseState *pstate, Node *relref, char *attname,
int location);
+ static bool isWindowFunction(Oid aggfnoid);
/*
***************
*** 63,69 **** static void unknown_attribute(ParseState *pstate, Node *relref, char *attname,
Node *
ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool agg_star, bool agg_distinct, bool func_variadic,
! bool is_column, int location)
{
Oid rettype;
Oid funcid;
--- 65,71 ----
Node *
ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool agg_star, bool agg_distinct, bool func_variadic,
! bool is_column, WinDef *windef, int location)
{
Oid rettype;
Oid funcid;
***************
*** 293,324 **** ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
}
else
{
! /* aggregate function */
! Aggref *aggref = makeNode(Aggref);
! aggref->aggfnoid = funcid;
! aggref->aggtype = rettype;
! aggref->args = fargs;
! aggref->aggstar = agg_star;
! aggref->aggdistinct = agg_distinct;
! aggref->location = location;
! /*
! * Reject attempt to call a parameterless aggregate without (*)
! * syntax. This is mere pedantry but some folks insisted ...
! */
! if (fargs == NIL && !agg_star)
! ereport(ERROR,
! (errcode(ERRCODE_WRONG_OBJECT_TYPE),
! errmsg("%s(*) must be used to call a parameterless aggregate function",
! NameListToString(funcname)),
! parser_errposition(pstate, location)));
! /* parse_agg.c does additional aggregate-specific processing */
! transformAggregateCall(pstate, aggref);
! retval = (Node *) aggref;
if (retset)
ereport(ERROR,
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
--- 295,371 ----
}
else
{
! /*
! * an aggregate function with window definition is
! * a window aggregate function
! */
! if (windef)
! {
! WinAggref *winagg = makeNode(WinAggref);
! winagg->aggfnoid = funcid;
! winagg->aggtype = rettype;
! winagg->args = fargs;
! winagg->location = location;
! /*
! * we are not sure if winagg can take star and distinct...
! * but currently the parser rejects these syntax.
! */
! winagg->aggstar = agg_star;
! winagg->aggdistinct = agg_distinct;
! if (agg_distinct)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("windowing functions cannot accept DISTINCT argument.")));
! /*
! * Reject attempt to call a parameterless aggregate without (*)
! * syntax. This is mere pedantry but some folks insisted ...
! */
! if (fargs == NIL && !agg_star &&
! !isWindowFunction(winagg->aggfnoid))
! ereport(ERROR,
! (errcode(ERRCODE_WRONG_OBJECT_TYPE),
! errmsg("%s(*) must be used to call a parameterless aggregate function",
! NameListToString(funcname)),
! parser_errposition(pstate, location)));
!
! transformWinAggregateCall(pstate, winagg);
!
! retval = (Node *) winagg;
! pstate->p_windef_list = lappend(pstate->p_windef_list, windef);
! windef->expr_list = lappend(windef->expr_list, winagg);
! }
! else
! {
! /* aggregate function */
! Aggref *aggref = makeNode(Aggref);
! aggref->aggfnoid = funcid;
! aggref->aggtype = rettype;
! aggref->args = fargs;
! aggref->aggstar = agg_star;
! aggref->aggdistinct = agg_distinct;
! aggref->location = location;
+ /*
+ * Reject attempt to call a parameterless aggregate without (*)
+ * syntax. This is mere pedantry but some folks insisted ...
+ */
+ if (fargs == NIL && !agg_star)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("%s(*) must be used to call a parameterless aggregate function",
+ NameListToString(funcname)),
+ parser_errposition(pstate, location)));
+
+ /* parse_agg.c does additional aggregate-specific processing */
+ transformAggregateCall(pstate, aggref);
+
+ retval = (Node *) aggref;
+
+ }
if (retset)
ereport(ERROR,
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
***************
*** 1373,1375 **** LookupAggNameTypeNames(List *aggname, List *argtypes, bool noError)
--- 1420,1448 ----
return oid;
}
+
+
+ /*
+ * is window function, not window aggregate?
+ */
+ static bool
+ isWindowFunction(Oid aggfnoid)
+ {
+ HeapTuple aggTuple;
+ Form_pg_aggregate aggform;
+ bool result = false;
+
+ aggTuple = SearchSysCache(AGGFNOID,
+ ObjectIdGetDatum(aggfnoid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(aggTuple))
+ elog(ERROR, "cache lookup failed for window aggregate %u", aggfnoid);
+ aggform = (Form_pg_aggregate) GETSTRUCT(aggTuple);
+ if (OidIsValid(aggform->aggfinalfn) &&
+ func_volatile(aggform->aggfinalfn) == PROVOLATILE_VOLATILE)
+ result = true;
+ ReleaseSysCache(aggTuple);
+
+ return result;
+ }
+
*** a/src/backend/rewrite/rewriteManip.c
--- b/src/backend/rewrite/rewriteManip.c
***************
*** 30,35 **** typedef struct
--- 30,37 ----
static bool contain_aggs_of_level_walker(Node *node,
contain_aggs_of_level_context *context);
+ static bool checkExprHasWinAggs_walker(Node *node,
+ contain_aggs_of_level_context *context);
static bool checkExprHasSubLink_walker(Node *node, void *context);
static Relids offset_relid_set(Relids relids, int offset);
static Relids adjust_relid_set(Relids relids, int oldrelid, int newrelid);
***************
*** 101,106 **** contain_aggs_of_level_walker(Node *node,
--- 103,152 ----
(void *) context);
}
+ bool
+ checkExprHasWinAggs(Node *node)
+ {
+ contain_aggs_of_level_context context;
+
+ context.sublevels_up = 0;
+
+ /*
+ * Must be prepared to start with a Query or a bare expression tree; if
+ * it's a Query, we don't want to increment sublevels_up.
+ */
+ return query_or_expression_tree_walker(node,
+ checkExprHasWinAggs_walker,
+ (void *) &context,
+ 0);
+ }
+
+ static bool
+ checkExprHasWinAggs_walker(Node *node, contain_aggs_of_level_context *context)
+ {
+ if (node == NULL)
+ return false;
+ if (IsA(node, WinAggref))
+ {
+ if (((WinAggref *) node)->agglevelsup == context->sublevels_up)
+ return true; /* abort the tree traversal and return true */
+ /* else fall through to examine argument */
+ }
+ if (IsA(node, Query))
+ {
+ /* Recurse into subselects */
+ bool result;
+
+ context->sublevels_up++;
+ result = query_tree_walker((Query *) node,
+ checkExprHasWinAggs_walker,
+ (void *) context, 0);
+ context->sublevels_up--;
+ return result;
+ }
+ return expression_tree_walker(node, checkExprHasWinAggs_walker,
+ (void *) context);
+ }
+
/*
* checkExprHasSubLink -
* Check if an expression contains a SubLink.
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 175,180 **** static void get_oper_expr(OpExpr *expr, deparse_context *context);
--- 175,181 ----
static void get_func_expr(FuncExpr *expr, deparse_context *context,
bool showimplicit);
static void get_agg_expr(Aggref *aggref, deparse_context *context);
+ static void get_winagg_expr(WinAggref *winagg, deparse_context *context);
static void get_coercion_expr(Node *arg, deparse_context *context,
Oid resulttype, int32 resulttypmod,
Node *parentNode);
***************
*** 3583,3588 **** get_rule_expr(Node *node, deparse_context *context,
--- 3584,3593 ----
get_agg_expr((Aggref *) node, context);
break;
+ case T_WinAggref:
+ get_winagg_expr((WinAggref *) node, context);
+ break;
+
case T_ArrayRef:
{
ArrayRef *aref = (ArrayRef *) node;
***************
*** 4541,4546 **** get_agg_expr(Aggref *aggref, deparse_context *context)
--- 4546,4585 ----
appendStringInfoChar(buf, ')');
}
+ /*
+ * get_winagg_expr - Parse back an WinAggref node
+ */
+ static void
+ get_winagg_expr(WinAggref *winagg, deparse_context *context)
+ {
+ StringInfo buf = context->buf;
+ Oid argtypes[FUNC_MAX_ARGS];
+ int nargs;
+ ListCell *l;
+
+ nargs = 0;
+ foreach(l, winagg->args)
+ {
+ if (nargs >= FUNC_MAX_ARGS)
+ ereport(ERROR,
+ (errcode(ERRCODE_TOO_MANY_ARGUMENTS),
+ errmsg("too many arguments")));
+ argtypes[nargs] = exprType((Node *) lfirst(l));
+ nargs++;
+ }
+
+ appendStringInfo(buf, "%s(%s",
+ generate_function_name(winagg->aggfnoid,
+ nargs, argtypes, NULL),
+ winagg->aggdistinct ? "DISTINCT " : "");
+ /* aggstar can be set only in zero-argument aggregates */
+ if (winagg->aggstar)
+ appendStringInfoChar(buf, '*');
+ else
+ get_rule_expr((Node *) winagg->args, context, true);
+ appendStringInfoChar(buf, ')');
+ }
+
/* ----------
* get_coercion_expr
*
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
***************
*** 228,237 **** slashUsage(unsigned short int pager)
fprintf(output, _(" \\pset NAME [VALUE] set table output option\n"
" (NAME := {format|border|expanded|fieldsep|footer|null|\n"
" numericlocale|recordsep|tuples_only|title|tableattr|pager})\n"));
! fprintf(output, _(" \\t [on|off] show only rows (currently %s)\n"),
ON(pset.popt.topt.tuples_only));
fprintf(output, _(" \\T [STRING] set HTML tag attributes, or unset if none\n"));
! fprintf(output, _(" \\x [on|off] toggle expanded output (currently %s)\n"),
ON(pset.popt.topt.expanded));
fprintf(output, "\n");
--- 228,237 ----
fprintf(output, _(" \\pset NAME [VALUE] set table output option\n"
" (NAME := {format|border|expanded|fieldsep|footer|null|\n"
" numericlocale|recordsep|tuples_only|title|tableattr|pager})\n"));
! fprintf(output, _(" \\t show only rows (currently %s)\n"),
ON(pset.popt.topt.tuples_only));
fprintf(output, _(" \\T [STRING] set HTML tag attributes, or unset if none\n"));
! fprintf(output, _(" \\x toggle expanded output (currently %s)\n"),
ON(pset.popt.topt.expanded));
fprintf(output, "\n");
*** a/src/include/catalog/pg_aggregate.h
--- b/src/include/catalog/pg_aggregate.h
***************
*** 220,225 **** DATA(insert ( 2243 bitor - 0 1560 _null_ ));
--- 220,233 ----
/* xml */
DATA(insert ( 2901 xmlconcat2 - 0 142 _null_ ));
+ /* window functions */
+ DATA(insert ( 3898 - row_number_final 0 20 _null_ ));
+ DATA(insert ( 3899 - rank_final 0 20 _null_ ));
+ DATA(insert ( 3900 - dense_rank_final 0 20 _null_ ));
+ DATA(insert ( 3901 int8inc percent_rank_final 0 20 "0" ));
+ DATA(insert ( 3902 int8inc cume_dist_final 0 20 "0" ));
+ DATA(insert ( 3903 ntile_trans ntile_final 0 1016 "{0,0}" ));
+
/*
* prototypes for functions in pg_aggregate.c
*/
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4571,4576 **** DATA(insert OID = 2948 ( txid_visible_in_snapshot PGNSP PGUID 12 1 0 0 f f t f
--- 4571,4598 ----
DESCR("is txid visible in snapshot?");
+ DATA(insert OID = 3790 ( row_number_final PGNSP PGUID 12 1 0 0 f f f f v 1 20 "20" _null_ _null_ _null_ row_number_final _null_ _null_ _null_));
+ DESCR("final function of row_number");
+ DATA(insert OID = 3791 ( rank_final PGNSP PGUID 12 1 0 0 f f f f v 1 20 "20" _null_ _null_ _null_ rank_final _null_ _null_ _null_));
+ DESCR("final function of rank");
+ DATA(insert OID = 3792 ( dense_rank_final PGNSP PGUID 12 1 0 0 f f f f v 1 20 "20" _null_ _null_ _null_ dense_rank_final _null_ _null_ _null_));
+ DESCR("final function of dense_rank");
+ DATA(insert OID = 3793 ( percent_rank_final PGNSP PGUID 12 1 0 0 f f f f v 1 701 "20" _null_ _null_ _null_ percent_rank_final _null_ _null_ _null_));
+ DESCR("final function of percent_rank");
+ DATA(insert OID = 3794 ( cume_dist_final PGNSP PGUID 12 1 0 0 f f f f v 1 701 "20" _null_ _null_ _null_ cume_dist_final _null_ _null_ _null_));
+ DESCR("final function of cume_dist");
+ DATA(insert OID = 3795 ( ntile_trans PGNSP PGUID 12 1 0 0 f f f f v 2 1016 "1016 20" _null_ _null_ _null_ ntile_trans _null_ _null_ _null_));
+ DESCR("transitional function of ntile");
+ DATA(insert OID = 3796 ( ntile_final PGNSP PGUID 12 1 0 0 f f f f v 1 20 "1016" _null_ _null_ _null_ ntile_final _null_ _null_ _null_));
+ DESCR("final function of ntile");
+
+ DATA(insert OID = 3898 ( row_number PGNSP PGUID 12 1 0 0 t f t f v 0 20 "" _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_));
+ DATA(insert OID = 3899 ( rank PGNSP PGUID 12 1 0 0 t f t f v 0 20 "" _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_));
+ DATA(insert OID = 3900 ( dense_rank PGNSP PGUID 12 1 0 0 t f t f v 0 20 "" _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_));
+ DATA(insert OID = 3901 ( percent_rank PGNSP PGUID 12 1 0 0 t f t f v 0 701 "" _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_));
+ DATA(insert OID = 3902 ( cume_dist PGNSP PGUID 12 1 0 0 t f t f v 0 701 "" _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_));
+ DATA(insert OID = 3903 ( ntile PGNSP PGUID 12 1 0 0 t f t f v 1 20 "20" _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_));
+
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
*** /dev/null
--- b/src/include/executor/nodeWindow.h
***************
*** 0 ****
--- 1,34 ----
+ /*-------------------------------------------------------------------------
+ *
+ * nodeWindow.h
+ * prototypes for nodeWindow.c
+ *
+ *
+ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * $Id$
+ *
+ *-------------------------------------------------------------------------
+ */
+ #ifndef NODEWINDOW_H
+ #define NODEWINDOW_H
+
+ #include "nodes/execnodes.h"
+
+ extern int ExecCountSlotsWindow(Window *node);
+ extern WindowState *ExecInitWindow(Window *node, EState *estate, int eflags);
+ extern TupleTableSlot *ExecWindow(WindowState *node);
+ extern void ExecEndWindow(WindowState *node);
+ extern void ExecReScanWindow(WindowState *node, ExprContext *exprCtxt);
+
+ /* SQL spec window functions */
+ extern Datum row_number_final(PG_FUNCTION_ARGS);
+ extern Datum rank_final(PG_FUNCTION_ARGS);
+ extern Datum dense_rank_final(PG_FUNCTION_ARGS);
+ extern Datum percent_rank_final(PG_FUNCTION_ARGS);
+ extern Datum cume_dist_final(PG_FUNCTION_ARGS);
+ extern Datum ntile_trans(PG_FUNCTION_ARGS);
+ extern Datum ntile_final(PG_FUNCTION_ARGS);
+
+ #endif /* NODEWINDOW_H */
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 123,128 **** typedef struct ExprContext
--- 123,132 ----
Datum *ecxt_aggvalues; /* precomputed values for Aggref nodes */
bool *ecxt_aggnulls; /* null flags for Aggref nodes */
+ /* Windowed values */
+ Datum *ecxt_winaggvalues; /* TODO: comment */
+ bool *ecxt_winaggnulls; /* TODO: comment */
+
/* Value to substitute for CaseTestExpr nodes in expression */
Datum caseValue_datum;
bool caseValue_isNull;
***************
*** 503,508 **** typedef struct AggrefExprState
--- 507,523 ----
} AggrefExprState;
/* ----------------
+ * WinAggrefExprState node
+ * ----------------
+ */
+ typedef struct WinAggrefExprState
+ {
+ ExprState xprstate;
+ List *args; /* states of argument expressions */
+ int aggno; /* ID number for agg within its plan node */
+ } WinAggrefExprState;
+
+ /* ----------------
* ArrayRefExprState node
*
* Note: array types can be fixed-length (typlen > 0), but only when the
***************
*** 1488,1491 **** typedef struct LimitState
--- 1503,1542 ----
TupleTableSlot *subSlot; /* tuple last obtained from subplan */
} LimitState;
+ /* these are private structures in nodeWindow.c */
+ typedef struct WindowStatePerAggData *WindowStatePerAgg;
+
+ /* ----------------
+ * WindowState -
+ *
+ * a state object used in nodeWindow.c. Similar to AggState, it holds
+ * another econtext for input tuples and per-agg information, but also
+ * WindowFrame list which is used to iterate inside the current partition and
+ * preserve the previous aggregated values.
+ * ----------------
+ */
+ typedef struct WindowState
+ {
+ ScanState ss; /* its first field is NodeTag */
+
+ FmgrInfo *prtEqfunctions; /* for partition by columns */
+ FmgrInfo *ordEqfunctions; /* for order by columns */
+ List *aggs; /* all WinAggref nodes in targetlist & quals */
+ int numaggs; /* number of aggregates */
+ WindowStatePerAgg peragg; /* per-WinAggref information */
+
+ TupleTableSlot *currentslot; /* represents CURRENT ROW */
+ ExprContext *tmpcontext; /* econetxt for input expression */
+ bool win_done; /* indicates completion of Window scan */
+ HeapTuple prt_firstTuple; /* copy of first tuple of current partition */
+ bool partition_processing; /* partition is in the process right now */
+ bool frame_processing; /* frame is in the process right now */
+ bool need_aggregate; /* whether trans functions are processed */
+
+ MemoryContext wincontext; /* memory context for long-lived data */
+
+ void *ts_partition; /* store whole of the current partition input */
+ int eflags; /* flags passed at InitNode */
+ } WindowState;
+
#endif /* EXECNODES_H */
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
***************
*** 67,72 **** typedef enum NodeTag
--- 67,73 ----
T_Hash,
T_SetOp,
T_Limit,
+ T_Window,
/*
* TAGS FOR PLAN STATE NODES (execnodes.h)
***************
*** 99,104 **** typedef enum NodeTag
--- 100,106 ----
T_HashState,
T_SetOpState,
T_LimitState,
+ T_WindowState,
/*
* TAGS FOR PRIMITIVE NODES (primnodes.h)
***************
*** 110,115 **** typedef enum NodeTag
--- 112,118 ----
T_Const,
T_Param,
T_Aggref,
+ T_WinAggref,
T_ArrayRef,
T_FuncExpr,
T_OpExpr,
***************
*** 156,161 **** typedef enum NodeTag
--- 159,165 ----
T_ExprState = 400,
T_GenericExprState,
T_AggrefExprState,
+ T_WinAggrefExprState,
T_ArrayRefExprState,
T_FuncExprState,
T_ScalarArrayOpExprState,
***************
*** 323,329 **** typedef enum NodeTag
--- 327,337 ----
T_ColumnRef,
T_ParamRef,
T_A_Const,
+ T_WinDef,
T_FuncCall,
+ T_OrderClause,
+ T_PartitionClause,
+ T_WindowClause,
T_A_Indices,
T_A_Indirection,
T_A_ArrayExpr,
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 115,120 **** typedef struct Query
--- 115,121 ----
bool hasAggs; /* has aggregates in tlist or havingQual */
bool hasSubLinks; /* has subquery SubLink */
bool hasDistinctOn; /* distinctClause is from DISTINCT ON */
+ bool hasWindow; /* has Window process */
List *rtable; /* list of range table entries */
FromExpr *jointree; /* table join tree (FROM and WHERE clauses) */
***************
*** 131,136 **** typedef struct Query
--- 132,139 ----
List *sortClause; /* a list of SortGroupClause's */
+ List *windowList;
+
Node *limitOffset; /* # of result tuples to skip (int8 expr) */
Node *limitCount; /* # of result tuples to return (int8 expr) */
***************
*** 267,272 **** typedef struct FuncCall
--- 270,276 ----
bool agg_star; /* argument was really '*' */
bool agg_distinct; /* arguments were labeled DISTINCT */
bool func_variadic; /* last argument was labeled VARIADIC */
+ struct WinDef *win_definition; /* window definition */
int location; /* token location, or -1 if unknown */
} FuncCall;
***************
*** 668,673 **** typedef struct SortGroupClause
--- 672,725 ----
} SortGroupClause;
/*
+ * OrderClause -
+ * representation of ORDER BY in Window
+ */
+ typedef SortGroupClause OrderClause;
+
+
+ /*
+ * PartitionClause -
+ * representaition of PATITION BY in Window
+ */
+ typedef SortGroupClause PartitionClause;
+
+ /*
+ * WinDef -
+ *
+ * This may come after function call expression or after HAVING clause.
+ * expr_list holds expression pointers attached with this window, which
+ * will be referred in the transformation process. Since window declaration
+ * appears more than once in a query, duplicated WinDefs are created, then
+ * are aggregated in transformation.
+ */
+ typedef struct WinDef
+ {
+ NodeTag type;
+ List *partitionClause;
+ List *orderClause;
+ List *expr_list;
+ char *name;
+ int location;
+ } WinDef;
+
+ /*
+ * WindowCaluse -
+ * A unit of Window.
+ *
+ * winref is an identification and may be referred by window expressions.
+ */
+ typedef struct WindowClause
+ {
+ NodeTag type;
+ List *partitionClause;
+ List *orderClause;
+ char *name;
+ Index winref;
+ } WindowClause;
+
+
+ /*
* RowMarkClause -
* representation of FOR UPDATE/SHARE clauses
*
***************
*** 765,770 **** typedef struct SelectStmt
--- 817,823 ----
Node *whereClause; /* WHERE qualification */
List *groupClause; /* GROUP BY clauses */
Node *havingClause; /* HAVING conditional-expression */
+ List *windowClause; /* WINDOW window_name AS (...), ... */
/*
* In a "leaf" node representing a VALUES list, the above fields are all
*** a/src/include/nodes/plannodes.h
--- b/src/include/nodes/plannodes.h
***************
*** 491,496 **** typedef struct Agg
--- 491,504 ----
long numGroups; /* estimated number of groups in input */
} Agg;
+ typedef struct Partition
+ {
+ Plan plan;
+ int numCols;
+ AttrNumber *prtColIdx;
+ Oid *prtOperators;
+ } Partition;
+
/* ----------------
* unique node
* ----------------
***************
*** 559,562 **** typedef struct Limit
--- 567,600 ----
Node *limitCount; /* COUNT parameter, or NULL if none */
} Limit;
+ /* ----------------
+ * window frame type
+ * ----------------
+ */
+ typedef enum WindowFrameType
+ {
+ WINDOW_FRAME_NONE,
+ WINDOW_FRAME_ROWS,
+ WINFOW_FRAME_RANGE
+ } WindowFrameType;
+
+ /* ----------------
+ * window node
+ * ----------------
+ */
+ typedef struct Window
+ {
+ Plan plan;
+ int prtNumCols; /* number of columns for partition boundary */
+ AttrNumber *prtColIdx; /* indices of partition boundary */
+ Oid *prtOperators; /* equation operators of partition columns */
+ int ordNumCols; /* number of columns for order keys */
+ AttrNumber *ordColIdx; /* indices of order keys */
+ Oid *ordOperators; /* equation operators of order columns */
+ WindowFrameType frameType; /* which type of frame? */
+ Node *preceding; /* reserved. PRECEDING ... Const Expr */
+ Node *following; /* reserved. FOLLOWING ... Const Expr */
+ Index winref; /* ID of this window, associated with winagg */
+ } Window;
+
#endif /* PLANNODES_H */
*** a/src/include/nodes/primnodes.h
--- b/src/include/nodes/primnodes.h
***************
*** 221,226 **** typedef struct Aggref
--- 221,239 ----
int location; /* token location, or -1 if unknown */
} Aggref;
+ typedef struct WinAggref
+ {
+ Expr xpr;
+ Oid aggfnoid; /* pg_proc Oid of the aggregate */
+ Oid aggtype; /* type Oid of result of the aggregate */
+ List *args; /* arguments to the aggregate */
+ Index agglevelsup; /* > 0 if agg belongs to outer query */
+ bool aggstar; /* TRUE if argument list was really '*' */
+ bool aggdistinct; /* TRUE if it's agg(DISTINCT ...) */
+ Index winref; /* tie with WinDef */
+ int location; /* token location, or -1 if unknown */
+ } WinAggref;
+
/* ----------------
* ArrayRef: describes an array subscripting operation
*
*** a/src/include/optimizer/planmain.h
--- b/src/include/optimizer/planmain.h
***************
*** 34,39 **** extern void query_planner(PlannerInfo *root, List *tlist,
--- 34,40 ----
*/
extern Plan *optimize_minmax_aggregates(PlannerInfo *root, List *tlist,
Path *best_path);
+ extern Aggref *find_aggref(Node *node);
/*
* prototypes for plan/createplan.c
***************
*** 66,71 **** extern SetOp *make_setop(SetOpCmd cmd, SetOpStrategy strategy, Plan *lefttree,
--- 67,75 ----
long numGroups, double outputRows);
extern Result *make_result(PlannerInfo *root, List *tlist,
Node *resconstantqual, Plan *subplan);
+ extern Window *make_window(PlannerInfo *root, List *tlist,
+ WindowClause *parse, Oid *prtOperators, Oid *ordOperators,
+ Plan *lefttree);
extern bool is_projection_capable_plan(Plan *plan);
/*
*** a/src/include/optimizer/var.h
--- b/src/include/optimizer/var.h
***************
*** 27,31 **** extern bool contain_vars_above_level(Node *node, int levelsup);
--- 27,34 ----
extern int find_minimum_var_level(Node *node);
extern List *pull_var_clause(Node *node, bool includeUpperVars);
extern Node *flatten_join_alias_vars(PlannerInfo *root, Node *node);
+ extern Node *find_winexpr_greater(TargetEntry *tle);
+ extern Node *find_winexpr_lesser(TargetEntry *tle);
+ extern Node *find_winexpr(TargetEntry *tle);
#endif /* VAR_H */
*** a/src/include/parser/parse_agg.h
--- b/src/include/parser/parse_agg.h
***************
*** 16,21 ****
--- 16,22 ----
#include "parser/parse_node.h"
extern void transformAggregateCall(ParseState *pstate, Aggref *agg);
+ extern void transformWinAggregateCall(ParseState *pstate, WinAggref *agg);
extern void parseCheckAggregates(ParseState *pstate, Query *qry);
*** a/src/include/parser/parse_clause.h
--- b/src/include/parser/parse_clause.h
***************
*** 30,41 **** extern List *transformGroupClause(ParseState *pstate, List *grouplist,
--- 30,57 ----
List **targetlist, List *sortClause);
extern List *transformSortClause(ParseState *pstate, List *orderlist,
List **targetlist, bool resolveUnknown);
+ extern List *transformOrderClause(ParseState *pstate, List *orderlist,
+ List **targetlist, bool resolveUnknown);
+ extern List *transformPartitionClause(ParseState *pstate, List *partitionlist,
+ List **targetlist);
+
+ extern List *transformWinDef(ParseState *pstate,
+ List *windefinition, List **targetlist);
+
+ extern List *addAllTargetsToSortList(ParseState *pstate,
+ List *sortlist, List *targetlist,
+ bool resolveUnknown);
+ extern List *addTargetToOrderList(ParseState *pstate, TargetEntry *tle,
+ List *orderlist, List *targetlist,
+ SortByDir sortby_dir, SortByNulls sortby_nulls,
+ List *sortby_opname, bool resolveUnknown);
extern List *transformDistinctClause(ParseState *pstate,
List **targetlist, List *sortClause);
extern List *transformDistinctOnClause(ParseState *pstate, List *distinctlist,
List **targetlist, List *sortClause);
extern Index assignSortGroupRef(TargetEntry *tle, List *tlist);
+ extern Index assignOverRef(TargetEntry *tle, List *tlist);
extern bool targetIsInSortList(TargetEntry *tle, Oid sortop, List *sortList);
#endif /* PARSE_CLAUSE_H */
*** a/src/include/parser/parse_func.h
--- b/src/include/parser/parse_func.h
***************
*** 44,50 **** typedef enum
extern Node *ParseFuncOrColumn(ParseState *pstate,
List *funcname, List *fargs,
bool agg_star, bool agg_distinct, bool func_variadic,
! bool is_column, int location);
extern FuncDetailCode func_get_detail(List *funcname, List *fargs,
int nargs, Oid *argtypes, bool expand_variadic,
--- 44,50 ----
extern Node *ParseFuncOrColumn(ParseState *pstate,
List *funcname, List *fargs,
bool agg_star, bool agg_distinct, bool func_variadic,
! bool is_column, WinDef *win_def, int location);
extern FuncDetailCode func_get_detail(List *funcname, List *fargs,
int nargs, Oid *argtypes, bool expand_variadic,
*** a/src/include/parser/parse_node.h
--- b/src/include/parser/parse_node.h
***************
*** 80,85 **** typedef struct ParseState
--- 80,87 ----
bool p_is_update;
Relation p_target_relation;
RangeTblEntry *p_target_rangetblentry;
+ List *p_windef_list;
+ bool p_hasWindow;
} ParseState;
extern ParseState *make_parsestate(ParseState *parentParseState);
*** a/src/include/rewrite/rewriteManip.h
--- b/src/include/rewrite/rewriteManip.h
***************
*** 37,42 **** extern void AddInvertedQual(Query *parsetree, Node *qual);
--- 37,43 ----
extern bool contain_aggs_of_level(Node *node, int levelsup);
extern bool checkExprHasAggs(Node *node);
+ extern bool checkExprHasWinAggs(Node *node);
extern bool checkExprHasSubLink(Node *node);
extern Node *ResolveNew(Node *node, int target_varno, int sublevels_up,
*** a/src/interfaces/ecpg/preproc/preproc.y
--- b/src/interfaces/ecpg/preproc/preproc.y
***************
*** 465,473 **** add_typedef(char *name, char * dimension, char * length, enum ECPGttype type_enu
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR ORDER
! OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
! PARSER PARTIAL PASSWORD PLACING PLANS POSITION
PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE
--- 465,473 ----
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR ORDER
! OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
! PARSER PARTIAL PARTITION PASSWORD PLACING PLANS POSITION
PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE
***************
*** 491,497 **** add_typedef(char *name, char * dimension, char * length, enum ECPGttype type_enu
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
VERBOSE VERSION_P VIEW VOLATILE
! WHEN WHERE WHITESPACE_P WITH WITHOUT WORK WRITE
XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLFOREST XMLPARSE
XMLPI XMLROOT XMLSERIALIZE
--- 491,497 ----
VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
VERBOSE VERSION_P VIEW VOLATILE
! WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRITE
XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLFOREST XMLPARSE
XMLPI XMLROOT XMLSERIALIZE
*** a/src/test/regress/expected/opr_sanity.out
--- b/src/test/regress/expected/opr_sanity.out
***************
*** 562,575 **** WHERE p1.oprjoin = p2.oid AND
(0 rows)
-- **************** pg_aggregate ****************
- -- Look for illegal values in pg_aggregate fields.
- SELECT ctid, aggfnoid::oid
- FROM pg_aggregate as p1
- WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0;
- ctid | aggfnoid
- ------+----------
- (0 rows)
-
-- Make sure the matching pg_proc entry is sensible, too.
SELECT a.aggfnoid::oid, p.proname
FROM pg_aggregate as a, pg_proc as p
--- 562,567 ----
*** /dev/null
--- b/src/test/regress/expected/window.out
***************
*** 0 ****
--- 1,122 ----
+ --
+ -- WINDOW FUNCTIONS
+ --
+ SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10;
+ sum_1 | ten | four
+ -------+-----+------
+ 2 | 0 | 0
+ 2 | 0 | 0
+ 2 | 0 | 2
+ 5 | 1 | 3
+ 5 | 1 | 1
+ 5 | 1 | 1
+ 3 | 3 | 3
+ 0 | 4 | 0
+ 1 | 7 | 1
+ 1 | 9 | 1
+ (10 rows)
+
+ SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10;
+ rank_1 | ten | four
+ --------+-----+------
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 3 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 3 | 7 | 1
+ 4 | 9 | 1
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ (10 rows)
+
+ SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum
+ FROM tenk1 GROUP BY ten, two;
+ ten | two | gsum | wsum
+ -----+-----+-------+--------
+ 0 | 0 | 45000 | 245000
+ 2 | 0 | 47000 | 245000
+ 4 | 0 | 49000 | 245000
+ 6 | 0 | 51000 | 245000
+ 8 | 0 | 53000 | 245000
+ 1 | 1 | 46000 | 250000
+ 3 | 1 | 48000 | 250000
+ 5 | 1 | 50000 | 250000
+ 7 | 1 | 52000 | 250000
+ 9 | 1 | 54000 | 250000
+ (10 rows)
+
+ SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
+ count | four
+ -------+------
+ 4 | 1
+ 4 | 1
+ 4 | 1
+ 4 | 1
+ 2 | 3
+ 2 | 3
+ (6 rows)
+
+ SELECT (count(*) OVER (PARTITION BY four ORDER BY ten) +
+ sum(hundred) OVER (PARTITION BY four ORDER BY ten))::varchar AS cntsum
+ FROM tenk1 WHERE unique2 < 10;
+ cntsum
+ --------
+ 87
+ 87
+ 87
+ 92
+ 92
+ 92
+ 92
+ 51
+ 136
+ 136
+ (10 rows)
+
+ -- opexpr with different windows evaluation.
+ SELECT * FROM(
+ SELECT count(*) OVER (PARTITION BY four ORDER BY ten) +
+ sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
+ count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
+ sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum
+ FROM tenk1
+ )sub
+ WHERE total <> fourcount + twosum;
+ total | fourcount | twosum
+ -------+-----------+--------
+ (0 rows)
+
+ SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10;
+ avg
+ ------------------------
+ 0.00000000000000000000
+ 0.00000000000000000000
+ 0.00000000000000000000
+ 1.00000000000000000000
+ 1.00000000000000000000
+ 1.00000000000000000000
+ 1.00000000000000000000
+ 2.0000000000000000
+ 3.0000000000000000
+ 3.0000000000000000
+ (10 rows)
+
+ -- WINDOW clause
+ SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum
+ FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten);
+ ten | two | gsum | wsum
+ -----+-----+-------+--------
+ 0 | 0 | 45000 | 245000
+ 2 | 0 | 47000 | 245000
+ 4 | 0 | 49000 | 245000
+ 6 | 0 | 51000 | 245000
+ 8 | 0 | 53000 | 245000
+ 1 | 1 | 46000 | 250000
+ 3 | 1 | 48000 | 250000
+ 5 | 1 | 50000 | 250000
+ 7 | 1 | 52000 | 250000
+ 9 | 1 | 54000 | 250000
+ (10 rows)
+
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
***************
*** 69,75 **** ignore: random
# ----------
# Another group of parallel tests
# ----------
! test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
test: privileges
test: misc
--- 69,75 ----
# ----------
# Another group of parallel tests
# ----------
! test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete window
test: privileges
test: misc
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
***************
*** 116,118 **** test: largeobject
--- 116,119 ----
test: xml
test: stats
test: tablespace
+ test: window
*** a/src/test/regress/sql/opr_sanity.sql
--- b/src/test/regress/sql/opr_sanity.sql
***************
*** 462,473 **** WHERE p1.oprjoin = p2.oid AND
-- **************** pg_aggregate ****************
- -- Look for illegal values in pg_aggregate fields.
-
- SELECT ctid, aggfnoid::oid
- FROM pg_aggregate as p1
- WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0;
-
-- Make sure the matching pg_proc entry is sensible, too.
SELECT a.aggfnoid::oid, p.proname
--- 462,467 ----
*** /dev/null
--- b/src/test/regress/sql/window.sql
***************
*** 0 ****
--- 1,32 ----
+ --
+ -- WINDOW FUNCTIONS
+ --
+
+ SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10;
+
+ SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10;
+
+ SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum
+ FROM tenk1 GROUP BY ten, two;
+
+ SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
+
+ SELECT (count(*) OVER (PARTITION BY four ORDER BY ten) +
+ sum(hundred) OVER (PARTITION BY four ORDER BY ten))::varchar AS cntsum
+ FROM tenk1 WHERE unique2 < 10;
+
+ -- opexpr with different windows evaluation.
+ SELECT * FROM(
+ SELECT count(*) OVER (PARTITION BY four ORDER BY ten) +
+ sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
+ count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
+ sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum
+ FROM tenk1
+ )sub
+ WHERE total <> fourcount + twosum;
+
+ SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10;
+
+ -- WINDOW clause
+ SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum
+ FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten);