*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 10041,10046 **** SELECT count(*) FROM sometable;
--- 10041,10155 ----
+
+ Window Functions
+
+
+ Window functions provides facilities of
+ windowed table calculation, including windowed aggregate and rank.
+
+ The built-in window rank functions are listed in
+ .
+ All of aggregate functions in and
+ can be also used in
+ windowed 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
+
+
+
+
+
+ row_number()
+
+ row_number() 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
+
+
+
+
+
+ ntile()
+
+ ntile(integer) OVER (ORDER BY expression)
+
+
+ integer
+
+ integer value ranging from 1 to the argument integer, equally dividing the window partition
+
+
+
+
+
+
Subquery Expressions
*** a/doc/src/sgml/keywords.sgml
--- b/doc/src/sgml/keywords.sgml
***************
*** 1510,1516 ****
EXCLUDE
!
non-reserved
--- 1510,1516 ----
EXCLUDE
! non-reserved
non-reserved
***************
*** 2868,2874 ****
OTHERS
!
non-reserved
--- 2868,2874 ----
OTHERS
! non-reserved
non-reserved
***************
*** 2896,2902 ****
OVER
!
reserved
--- 2896,2902 ----
OVER
! non-reserved
reserved
***************
*** 3015,3021 ****
PARTITION
!
reserved
--- 3015,3021 ----
PARTITION
! reserved
reserved
***************
*** 3106,3112 ****
PRECEDING
!
non-reserved
--- 3106,3112 ----
PRECEDING
! reserved
non-reserved
***************
*** 3204,3210 ****
RANGE
!
reserved
--- 3204,3210 ----
RANGE
! reserved
reserved
***************
*** 4128,4134 ****
TIES
!
non-reserved
--- 4128,4134 ----
TIES
! non-reserved
non-reserved
***************
*** 4317,4323 ****
UNBOUNDED
!
non-reserved
--- 4317,4323 ----
UNBOUNDED
! non-reserved
non-reserved
***************
*** 4590,4596 ****
WINDOW
!
reserved
--- 4590,4596 ----
WINDOW
! reserved
reserved
*** a/doc/src/sgml/query.sgml
--- b/doc/src/sgml/query.sgml
***************
*** 805,810 **** SELECT city, max(temp_lo)
--- 805,909 ----
+
+ Window Functions
+
+
+ A window function is the operation across a set of rows in
+ a windowed table. This may sound similar to aggregate functions,
+ but contrast to that window functions don't reduce rows.
+ Additionally, window 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 window 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 in the partition are ordered by the specified value. In this case,
+ in each department each employee's salary rank with gap is shown.
+
+
+
+ Window functions are put in the SELECT list.
+ It is forbidden anywhere else such as GROUP BY,
+ HAVING and WHERE clauses.
+ The arguments passed to window functions and the expressions in
+ PARTITION BY and ORDER BY
+ of a window definition can be the results of aggregate functions.
+ But window functions may not be placed as aggregate functions'
+ arguments. All of window 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,1385 ----
+ /*-------------------------------------------------------------------------
+ *
+ * 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);
+
+ /*
+ * we check it again because store_partition may hit the
+ * end of rows without storing anything.
+ */
+ if (winstate->win_done && !winstate->partition_processing)
+ return NULL;
+
+ 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
***************
*** 691,696 **** _copyLimit(Limit *from)
--- 691,718 ----
}
/*
+ * _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;
+ }
+
+ /*
* _copyPlanInvalItem
*/
static PlanInvalItem *
***************
*** 861,866 **** _copyAggref(Aggref *from)
--- 883,908 ----
}
/*
+ * _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 *
***************
*** 1620,1625 **** _copySortGroupClause(SortGroupClause *from)
--- 1662,1720 ----
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)
{
***************
*** 1709,1714 **** _copyFuncCall(FuncCall *from)
--- 1804,1810 ----
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;
***************
*** 1931,1936 **** _copyQuery(Query *from)
--- 2027,2033 ----
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);
***************
*** 1939,1944 **** _copyQuery(Query *from)
--- 2036,2042 ----
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);
***************
*** 1999,2004 **** _copySelectStmt(SelectStmt *from)
--- 2097,2103 ----
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);
***************
*** 3173,3178 **** copyObject(void *from)
--- 3272,3279 ----
case T_Limit:
retval = _copyLimit(from);
break;
+ case T_Window:
+ retval = _copyWindow(from);
case T_PlanInvalItem:
retval = _copyPlanInvalItem(from);
break;
***************
*** 3201,3206 **** copyObject(void *from)
--- 3302,3310 ----
case T_Aggref:
retval = _copyAggref(from);
break;
+ case T_WinAggref:
+ retval = _copyWinAggref(from);
+ break;
case T_ArrayRef:
retval = _copyArrayRef(from);
break;
***************
*** 3669,3674 **** copyObject(void *from)
--- 3773,3790 ----
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
***************
*** 192,197 **** _equalAggref(Aggref *a, Aggref *b)
--- 192,212 ----
}
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);
***************
*** 868,873 **** _equalSelectStmt(SelectStmt *a, SelectStmt *b)
--- 883,889 ----
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);
***************
*** 1761,1766 **** _equalFuncCall(FuncCall *a, FuncCall *b)
--- 1777,1783 ----
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;
***************
*** 1960,1965 **** _equalSortGroupClause(SortGroupClause *a, SortGroupClause *b)
--- 1977,2005 ----
}
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);
***************
*** 2135,2140 **** equal(void *a, void *b)
--- 2175,2182 ----
break;
case T_Aggref:
retval = _equalAggref(a, b);
+ case T_WinAggref:
+ retval = _equalWinAggref(a, b);
break;
case T_ArrayRef:
retval = _equalArrayRef(a, b);
***************
*** 2241,2246 **** equal(void *a, void *b)
--- 2283,2291 ----
case T_JoinExpr:
retval = _equalJoinExpr(a, b);
break;
+ case T_WindowClause:
+ retval = _equalWindowClause(a, b);
+ break;
/*
* RELATION NODES
***************
*** 2588,2595 **** equal(void *a, void *b)
--- 2633,2645 ----
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;
***************
*** 1030,1035 **** expression_tree_walker(Node *node,
--- 1033,1046 ----
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;
***************
*** 1498,1503 **** expression_tree_mutator(Node *node,
--- 1509,1524 ----
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
***************
*** 640,645 **** _outLimit(StringInfo str, Limit *node)
--- 640,667 ----
}
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
_outPlanInvalItem(StringInfo str, PlanInvalItem *node)
{
WRITE_NODE_TYPE("PLANINVALITEM");
***************
*** 755,760 **** _outAggref(StringInfo str, Aggref *node)
--- 777,797 ----
}
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");
***************
*** 1648,1653 **** _outSelectStmt(StringInfo str, SelectStmt *node)
--- 1685,1691 ----
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);
***************
*** 1669,1674 **** _outFuncCall(StringInfo str, FuncCall *node)
--- 1707,1713 ----
WRITE_BOOL_FIELD(agg_star);
WRITE_BOOL_FIELD(agg_distinct);
WRITE_BOOL_FIELD(func_variadic);
+ WRITE_NODE_FIELD(win_definition);
WRITE_LOCATION_FIELD(location);
}
***************
*** 1793,1798 **** _outQuery(StringInfo str, Query *node)
--- 1832,1838 ----
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);
***************
*** 1801,1806 **** _outQuery(StringInfo str, Query *node)
--- 1841,1847 ----
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);
***************
*** 1819,1824 **** _outSortGroupClause(StringInfo str, SortGroupClause *node)
--- 1860,1911 ----
}
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");
***************
*** 2228,2233 **** _outNode(StringInfo str, void *obj)
--- 2315,2323 ----
case T_Limit:
_outLimit(str, obj);
break;
+ case T_Window:
+ _outWindow(str, obj);
+ break;
case T_PlanInvalItem:
_outPlanInvalItem(str, obj);
break;
***************
*** 2252,2257 **** _outNode(StringInfo str, void *obj)
--- 2342,2350 ----
case T_Aggref:
_outAggref(str, obj);
break;
+ case T_WinAggref:
+ _outWinAggref(str, obj);
+ break;
case T_ArrayRef:
_outArrayRef(str, obj);
break;
***************
*** 2470,2475 **** _outNode(StringInfo str, void *obj)
--- 2563,2580 ----
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 *
***************
*** 378,383 **** _readAggref(void)
--- 445,470 ----
}
/*
+ * _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 *
***************
*** 1058,1063 **** parseNodeString(void)
--- 1145,1158 ----
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))
***************
*** 1076,1081 **** parseNodeString(void)
--- 1171,1178 ----
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
***************
*** 3220,3225 **** make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount,
--- 3220,3346 ----
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);
/*****************************************************************************
***************
*** 1208,1213 **** grouping_planner(PlannerInfo *root, double tuple_fraction)
--- 1211,1299 ----
} /* 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;
+
+ /*
+ * If the top-level plan node is one that cannot do expression
+ * evaluation, we must insert a Result node to project the
+ * desired tlist.
+ */
+ if (!is_projection_capable_plan(result_plan))
+ {
+ result_plan = (Plan *) make_result(root,
+ tlist,
+ NULL,
+ result_plan);
+ }
+ 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;
+
+ 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);
+ window_pathkeys = canonicalize_pathkeys(root, window_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)
***************
*** 2010,2016 **** 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;
--- 2096,2102 ----
* 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;
***************
*** 2170,2172 **** postprocess_setop_tlist(List *new_tlist, List *orig_tlist)
--- 2256,2463 ----
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
***************
*** 392,397 **** set_plan_refs(PlannerGlobal *glob, Plan *plan, int rtoffset)
--- 392,398 ----
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
***************
*** 779,784 **** contain_volatile_functions_walker(Node *node, void *context)
--- 779,791 ----
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
***************
*** 59,64 **** typedef struct
--- 59,70 ----
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);
***************
*** 75,80 **** static bool pull_var_clause_walker(Node *node,
--- 81,88 ----
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);
/*
***************
*** 747,749 **** alias_relid_set(PlannerInfo *root, Relids relids)
--- 755,827 ----
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
***************
*** 751,757 **** transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
qry->hasDistinctOn = true;
}
! /* transform LIMIT */
qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset,
"OFFSET");
qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
--- 751,761 ----
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,
***************
*** 770,778 **** transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
--- 774,786 ----
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);
+ if (pstate->p_hasWindow)
+ parseCheckWindow(pstate, qry);
+
foreach(l, stmt->lockingClause)
{
transformLockingClause(pstate, qry, (LockingClause *) lfirst(l));
***************
*** 1521,1528 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
qry->hasSubLinks = pstate->p_hasSubLinks;
/*
! * Top-level aggregates are simply disallowed in UPDATE, per spec. (From
! * an implementation point of view, this is forced because the implicit
* ctid reference would otherwise be an ungrouped variable.)
*/
if (pstate->p_hasAggs)
--- 1529,1536 ----
qry->hasSubLinks = pstate->p_hasSubLinks;
/*
! * Top-level aggregates nor window are simply disallowed in UPDATE, per spec.
! * (From an implementation point of view, this is forced because the implicit
* ctid reference would otherwise be an ungrouped variable.)
*/
if (pstate->p_hasAggs)
***************
*** 1532,1537 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
--- 1540,1550 ----
parser_errposition(pstate,
locate_agg_of_level((Node *) qry, 0))));
+ if (pstate->p_hasWindow)
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("cannot use window function in UPDATE")));
+
/*
* Now we are done with SELECT-like processing, and can get on with
* transforming the target list to match the UPDATE target columns.
***************
*** 1631,1636 **** transformReturningList(ParseState *pstate, List *returningList)
--- 1644,1655 ----
parser_errposition(pstate,
locate_agg_of_level((Node *) rlist, 0))));
+ /* window functions not allowed in returning clause */
+ if (pstate->p_hasWindow)
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("cannot use window function in RETURNING")));
+
/* no new relation references please */
if (list_length(pstate->p_rtable) != length_rtable)
{
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 377,382 **** static TypeName *TableFuncTypeName(List *columns);
--- 377,386 ----
%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
+ /* since these are not implemented, types may be unmatched actually. */
+ %type opt_frame_clause frame_clause frame_extent frame_bound opt_frame_exclusion
/*
* If you make any token changes, update the keyword table in
***************
*** 405,414 **** static TypeName *TableFuncTypeName(List *columns);
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
! EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT EXCLUDING
! EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
! FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD
FREEZE FROM FULL FUNCTION
GLOBAL GRANT GRANTED GREATEST GROUP_P
--- 409,418 ----
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
! EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT EXCLUDE
! EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
! FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
FREEZE FROM FULL FUNCTION
GLOBAL GRANT GRANTED GREATEST GROUP_P
***************
*** 435,449 **** 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
QUOTE
! READ REAL REASSIGN RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME
REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE
RIGHT ROLE ROLLBACK ROW ROWS RULE
--- 439,453 ----
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
! ORDER OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
! PARSER PARTIAL PARTITION PASSWORD PLACING PLANS POSITION
! PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE
QUOTE
! RANGE READ REAL REASSIGN RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME
REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE
RIGHT ROLE ROLLBACK ROW ROWS RULE
***************
*** 453,469 **** static TypeName *TableFuncTypeName(List *columns);
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
! UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNTIL
UPDATE USER USING
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
--- 457,473 ----
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIES TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
! UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNTIL
UPDATE USER USING
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
***************
*** 6318,6324 **** 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;
--- 6322,6328 ----
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;
***************
*** 6328,6333 **** simple_select:
--- 6332,6338 ----
n->whereClause = $6;
n->groupClause = $7;
n->havingClause = $8;
+ n->windowClause = $9;
$$ = (Node *)n;
}
| values_clause { $$ = $1; }
***************
*** 8007,8013 **** 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;
--- 8012,8018 ----
* (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;
***************
*** 8015,8024 **** 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;
--- 8020,8030 ----
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;
***************
*** 8026,8035 **** 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;
--- 8032,8042 ----
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;
***************
*** 8037,8046 **** 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;
--- 8044,8054 ----
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;
***************
*** 8048,8057 **** 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;
--- 8056,8066 ----
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;
***************
*** 8063,8072 **** 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;
--- 8072,8082 ----
* 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;
***************
*** 8074,8083 **** 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
--- 8084,8094 ----
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
***************
*** 8095,8100 **** func_expr: func_name '(' ')'
--- 8106,8112 ----
n->agg_star = TRUE;
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
+ n->win_definition = (WinDef *) $5;
n->location = @1;
$$ = (Node *)n;
}
***************
*** 8520,8525 **** xml_whitespace_option: PRESERVE WHITESPACE_P { $$ = TRUE; }
--- 8532,8645 ----
;
/*
+ * 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 opt_frame_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;
+ }
+ ;
+
+ /* Frame clause is not supported but we must recognize its grammar to report errors */
+ opt_frame_clause:
+ frame_clause
+ {
+ /* Frame clause should be called "FRAME clause" or "ROWS/RANGE clause"? */
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ROWS/RANGE clause of window functions not yet implemented")));
+ }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+ frame_clause:
+ ROWS frame_extent opt_frame_exclusion
+ { $$ = NULL; }
+ | RANGE frame_extent opt_frame_exclusion
+ { $$ = NULL; }
+ ;
+ frame_extent:
+ UNBOUNDED PRECEDING { $$ = NULL; }
+ | CURRENT_P ROW { $$ = NULL; }
+ | a_expr PRECEDING { $$ = NULL; }
+ | BETWEEN frame_bound AND frame_bound { $$ = NULL; }
+ ;
+ frame_bound:
+ UNBOUNDED PRECEDING { $$ = NULL; }
+ | CURRENT_P ROW { $$ = NULL; }
+ | a_expr PRECEDING { $$ = NULL; }
+ | UNBOUNDED FOLLOWING { $$ = NULL; }
+ | a_expr FOLLOWING { $$ = NULL; }
+ ;
+ opt_frame_exclusion:
+ EXCLUDE CURRENT_P ROW { $$ = NULL; }
+ | EXCLUDE GROUP_P { $$ = NULL; }
+ | EXCLUDE TIES { $$ = NULL; }
+ | EXCLUDE NO OTHERS { $$ = NULL; }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
+ 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
***************
*** 155,160 **** const ScanKeyword ScanKeywords[] = {
--- 155,161 ----
{"enum", ENUM_P, UNRESERVED_KEYWORD},
{"escape", ESCAPE, UNRESERVED_KEYWORD},
{"except", EXCEPT, RESERVED_KEYWORD},
+ {"exclude", EXCLUDE, UNRESERVED_KEYWORD},
{"excluding", EXCLUDING, UNRESERVED_KEYWORD},
{"exclusive", EXCLUSIVE, UNRESERVED_KEYWORD},
{"execute", EXECUTE, UNRESERVED_KEYWORD},
***************
*** 167,172 **** const ScanKeyword ScanKeywords[] = {
--- 168,174 ----
{"fetch", FETCH, UNRESERVED_KEYWORD},
{"first", FIRST_P, UNRESERVED_KEYWORD},
{"float", FLOAT_P, COL_NAME_KEYWORD},
+ {"following", FOLLOWING, UNRESERVED_KEYWORD},
{"for", FOR, RESERVED_KEYWORD},
{"force", FORCE, UNRESERVED_KEYWORD},
{"foreign", FOREIGN, RESERVED_KEYWORD},
***************
*** 278,295 **** const ScanKeyword ScanKeywords[] = {
--- 280,301 ----
{"option", OPTION, UNRESERVED_KEYWORD},
{"or", OR, RESERVED_KEYWORD},
{"order", ORDER, RESERVED_KEYWORD},
+ {"others", OTHERS, UNRESERVED_KEYWORD},
{"out", OUT_P, COL_NAME_KEYWORD},
{"outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD},
+ {"over", OVER, UNRESERVED_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},
{"position", POSITION, COL_NAME_KEYWORD},
+ {"preceding", PRECEDING, UNRESERVED_KEYWORD},
{"precision", PRECISION, COL_NAME_KEYWORD},
{"prepare", PREPARE, UNRESERVED_KEYWORD},
{"prepared", PREPARED, UNRESERVED_KEYWORD},
***************
*** 300,305 **** const ScanKeyword ScanKeywords[] = {
--- 306,312 ----
{"procedural", PROCEDURAL, UNRESERVED_KEYWORD},
{"procedure", PROCEDURE, UNRESERVED_KEYWORD},
{"quote", QUOTE, UNRESERVED_KEYWORD},
+ {"range", RANGE, RESERVED_KEYWORD},
{"read", READ, UNRESERVED_KEYWORD},
{"real", REAL, COL_NAME_KEYWORD},
{"reassign", REASSIGN, UNRESERVED_KEYWORD},
***************
*** 365,370 **** const ScanKeyword ScanKeywords[] = {
--- 372,378 ----
{"temporary", TEMPORARY, UNRESERVED_KEYWORD},
{"text", TEXT_P, UNRESERVED_KEYWORD},
{"then", THEN, RESERVED_KEYWORD},
+ {"ties", TIES, UNRESERVED_KEYWORD},
{"time", TIME, COL_NAME_KEYWORD},
{"timestamp", TIMESTAMP, COL_NAME_KEYWORD},
{"to", TO, RESERVED_KEYWORD},
***************
*** 377,382 **** const ScanKeyword ScanKeywords[] = {
--- 385,391 ----
{"truncate", TRUNCATE, UNRESERVED_KEYWORD},
{"trusted", TRUSTED, UNRESERVED_KEYWORD},
{"type", TYPE_P, UNRESERVED_KEYWORD},
+ {"unbounded", UNBOUNDED, UNRESERVED_KEYWORD},
{"uncommitted", UNCOMMITTED, UNRESERVED_KEYWORD},
{"unencrypted", UNENCRYPTED, UNRESERVED_KEYWORD},
{"union", UNION, RESERVED_KEYWORD},
***************
*** 402,407 **** const ScanKeyword ScanKeywords[] = {
--- 411,417 ----
{"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
***************
*** 1,7 ****
/*-------------------------------------------------------------------------
*
* parse_agg.c
! * handle aggregates in parser
*
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
--- 1,7 ----
/*-------------------------------------------------------------------------
*
* parse_agg.c
! * handle aggregates and window functions in parser
*
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
***************
*** 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"),
***************
*** 85,90 **** transformAggregateCall(ParseState *pstate, Aggref *agg)
--- 85,124 ----
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;
+ pstate->p_hasWindow = true;
+ }
/*
* parseCheckAggregates
***************
*** 219,224 **** parseCheckAggregates(ParseState *pstate, Query *qry)
--- 253,293 ----
groupClauses, have_non_var_grouping);
}
+ /*
+ * Window expressions must not be in either WHERE, HAVING, or GRUP BY clauses.
+ */
+ void
+ parseCheckWindow(ParseState *pstate, Query *qry)
+ {
+ ListCell *l;
+
+ if (checkExprHasWinAggs(qry->jointree->quals))
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("window functions not allowed in WHERE clause")));
+ if (checkExprHasWinAggs((Node *) qry->jointree->fromlist))
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("window functions not allowed in JOIN conditions")));
+ if (checkExprHasWinAggs(qry->havingQual))
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("window functions not allowed in HAVING clause")));
+
+ foreach(l, qry->groupClause)
+ {
+ SortGroupClause *grpcl = (SortGroupClause *) lfirst(l);
+ Node *expr;
+
+ expr = get_sortgroupclause_expr(grpcl, qry->targetList);
+ if (expr == NULL)
+ continue; /* probably cannot happen */
+ if (checkExprHasWinAggs(expr))
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("window functions not allowed in GROUP BY clause")));
+ }
+ }
/*
* check_ungrouped_columns -
*** 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 PARTITION_CLAUSE 3
+ #define WIN_ORDER_CLAUSE 4
! static char *clauseText[] = {"ORDER BY", "GROUP BY", "DISTINCT ON", "PARTITION", "ORDER BY"};
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 int get_matching_location(int sortgroupref,
List *sortgrouprefs, List *exprs);
static List *addTargetToSortList(ParseState *pstate, TargetEntry *tle,
***************
*** 549,554 **** transformRangeFunction(ParseState *pstate, RangeFunction *r)
--- 553,566 ----
locate_agg_of_level(funcexpr, 0))));
}
+ if (pstate->p_hasWindow)
+ {
+ if (checkExprHasWinAggs(funcexpr))
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("cannot use window function in function expression in FROM")));
+ }
+
/*
* OK, build an RTE for the function.
*/
***************
*** 1270,1275 **** findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause)
--- 1282,1312 ----
clauseText[clause]),
parser_errposition(pstate, location)));
+ /*
+ * In ORDER BY clause of a window, simple integer means
+ * integer data, not target pos.
+ */
+ if (clause == WIN_ORDER_CLAUSE)
+ {
+ TargetEntry *tle;
+ Const *cons;
+ AttrNumber resno;
+
+ resno = list_length(*tlist) + 1;
+ cons = makeConst(INT4OID,
+ -1,
+ sizeof(int32),
+ Int32GetDatum(intVal(val)),
+ false,
+ true);
+ tle = makeTargetEntry((Expr *) cons,
+ resno,
+ NULL,
+ true);
+ *tlist = lappend(*tlist, tle);
+ return tle;
+ }
+
target_pos = intVal(val);
foreach(tl, *tlist)
{
***************
*** 1421,1426 **** transformSortClause(ParseState *pstate,
--- 1458,1609 ----
}
/*
+ * 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, WIN_ORDER_CLAUSE);
+ result = addTargetToSortList(pstate, tle,
+ result, *targetlist,
+ sortby, 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)
+ {
+ /*
+ * 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
*
***************
*** 1888,1890 **** targetIsInSortList(TargetEntry *tle, Oid sortop, List *sortList)
--- 2071,2101 ----
}
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
***************
*** 360,366 **** transformIndirection(ParseState *pstate, Node *basenode, List *indirection)
list_make1(n),
list_make1(result),
false, false, false,
! true, -1);
}
}
/* process trailing subscripts, if any */
--- 360,366 ----
list_make1(n),
list_make1(result),
false, false, false,
! true, NULL, -1);
}
}
/* process trailing subscripts, if any */
***************
*** 504,510 **** transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(name2)),
list_make1(node),
false, false, false,
! true, cref->location);
}
break;
}
--- 504,510 ----
list_make1(makeString(name2)),
list_make1(node),
false, false, false,
! true, NULL, cref->location);
}
break;
}
***************
*** 545,551 **** transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(name3)),
list_make1(node),
false, false, false,
! true, cref->location);
}
break;
}
--- 545,551 ----
list_make1(makeString(name3)),
list_make1(node),
false, false, false,
! true, NULL, cref->location);
}
break;
}
***************
*** 600,606 **** transformColumnRef(ParseState *pstate, ColumnRef *cref)
list_make1(makeString(name4)),
list_make1(node),
false, false, false,
! true, cref->location);
}
break;
}
--- 600,606 ----
list_make1(makeString(name4)),
list_make1(node),
false, false, false,
! true, NULL, cref->location);
}
break;
}
***************
*** 1096,1101 **** transformFuncCall(ParseState *pstate, FuncCall *fn)
--- 1096,1102 ----
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;
***************
*** 294,325 **** 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),
--- 296,372 ----
}
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),
***************
*** 1374,1376 **** LookupAggNameTypeNames(List *aggname, List *argtypes, bool noError)
--- 1421,1449 ----
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
***************
*** 36,41 **** typedef struct
--- 36,43 ----
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 locate_agg_of_level_walker(Node *node,
locate_agg_of_level_context *context);
static bool checkExprHasSubLink_walker(Node *node, void *context);
***************
*** 110,115 **** contain_aggs_of_level_walker(Node *node,
--- 112,161 ----
(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);
+ }
+
/*
* locate_agg_of_level -
* Find the parse location of any aggregate of the specified query level.
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 176,181 **** static void get_oper_expr(OpExpr *expr, deparse_context *context);
--- 176,182 ----
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);
***************
*** 3768,3773 **** get_rule_expr(Node *node, deparse_context *context,
--- 3769,3778 ----
get_agg_expr((Aggref *) node, context);
break;
+ case T_WinAggref:
+ get_winagg_expr((WinAggref *) node, context);
+ break;
+
case T_ArrayRef:
{
ArrayRef *aref = (ArrayRef *) node;
***************
*** 4726,4731 **** get_agg_expr(Aggref *aggref, deparse_context *context)
--- 4731,4770 ----
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/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
***************
*** 4573,4578 **** DATA(insert OID = 2948 ( txid_visible_in_snapshot PGNSP PGUID 12 1 0 0 f f t f
--- 4573,4600 ----
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,
/* this one isn't a subclass of Plan: */
T_PlanInvalItem,
***************
*** 101,106 **** typedef enum NodeTag
--- 102,108 ----
T_HashState,
T_SetOpState,
T_LimitState,
+ T_WindowState,
/*
* TAGS FOR PRIMITIVE NODES (primnodes.h)
***************
*** 112,117 **** typedef enum NodeTag
--- 114,120 ----
T_Const,
T_Param,
T_Aggref,
+ T_WinAggref,
T_ArrayRef,
T_FuncExpr,
T_OpExpr,
***************
*** 158,163 **** typedef enum NodeTag
--- 161,167 ----
T_ExprState = 400,
T_GenericExprState,
T_AggrefExprState,
+ T_WinAggrefExprState,
T_ArrayRefExprState,
T_FuncExprState,
T_ScalarArrayOpExprState,
***************
*** 325,331 **** typedef enum NodeTag
--- 329,339 ----
T_ColumnRef,
T_ParamRef,
T_A_Const,
+ T_WinDef,
T_FuncCall,
+ T_OrderClause,
+ T_PartitionClause,
+ T_WindowClause,
T_A_Star,
T_A_Indices,
T_A_Indirection,
*** 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) */
***************
*** 269,274 **** typedef struct FuncCall
--- 272,278 ----
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;
***************
*** 684,689 **** typedef struct SortGroupClause
--- 688,741 ----
} 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
*
***************
*** 781,786 **** typedef struct SelectStmt
--- 833,839 ----
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
***************
*** 494,499 **** typedef struct Agg
--- 494,507 ----
long numGroups; /* estimated number of groups in input */
} Agg;
+ typedef struct Partition
+ {
+ Plan plan;
+ int numCols;
+ AttrNumber *prtColIdx;
+ Oid *prtOperators;
+ } Partition;
+
/* ----------------
* unique node
* ----------------
***************
*** 562,567 **** typedef struct Limit
--- 570,604 ----
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;
/*
* Plan invalidation info
*** a/src/include/nodes/primnodes.h
--- b/src/include/nodes/primnodes.h
***************
*** 222,227 **** typedef struct Aggref
--- 222,240 ----
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
***************
*** 26,30 **** extern int locate_var_of_relation(Node *node, int relid, int levelsup);
--- 26,33 ----
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
***************
*** 1,7 ****
/*-------------------------------------------------------------------------
*
* parse_agg.h
! * handle aggregates in parser
*
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
--- 1,7 ----
/*-------------------------------------------------------------------------
*
* parse_agg.h
! * handle aggregates and window functions in parser
*
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
***************
*** 16,23 ****
--- 16,25 ----
#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);
+ extern void parseCheckWindow(ParseState *pstate, Query *qry);
extern void build_aggregate_fnexprs(Oid *agg_input_types,
int agg_num_inputs,
*** 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;
/* Support for parser_errposition_callback function */
*** a/src/include/rewrite/rewriteManip.h
--- b/src/include/rewrite/rewriteManip.h
***************
*** 38,43 **** extern void AddInvertedQual(Query *parsetree, Node *qual);
--- 38,44 ----
extern bool contain_aggs_of_level(Node *node, int levelsup);
extern int locate_agg_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/include/utils/errcodes.h
--- b/src/include/utils/errcodes.h
***************
*** 246,251 ****
--- 246,252 ----
#define ERRCODE_INSUFFICIENT_PRIVILEGE MAKE_SQLSTATE('4','2', '5','0','1')
#define ERRCODE_CANNOT_COERCE MAKE_SQLSTATE('4','2', '8','4','6')
#define ERRCODE_GROUPING_ERROR MAKE_SQLSTATE('4','2', '8','0','3')
+ #define ERRCODE_WINDOWING_ERROR MAKE_SQLSTATE('4','2', '8','1','3')
#define ERRCODE_INVALID_FOREIGN_KEY MAKE_SQLSTATE('4','2', '8','3','0')
#define ERRCODE_INVALID_NAME MAKE_SQLSTATE('4','2', '6','0','2')
#define ERRCODE_NAME_TOO_LONG MAKE_SQLSTATE('4','2', '6','2','2')
*** a/src/interfaces/ecpg/preproc/preproc.y
--- b/src/interfaces/ecpg/preproc/preproc.y
***************
*** 435,444 **** add_typedef(char *name, char * dimension, char * length, enum ECPGttype type_enu
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
! EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT EXCLUSIVE EXCLUDING
! EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
! FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD FREEZE FROM
FULL FUNCTION
GLOBAL GRANT GRANTED GREATEST GROUP_P
--- 435,444 ----
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
! EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT EXCLUDE EXCLUSIVE
! EXCLUDING EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
! FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD FREEZE FROM
FULL FUNCTION
GLOBAL GRANT GRANTED GREATEST GROUP_P
***************
*** 465,479 **** 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
QUOTE
! READ REAL REASSIGN RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME
REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE
RIGHT ROLE ROLLBACK ROW ROWS RULE
--- 465,479 ----
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR ORDER
! OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
! PARSER PARTIAL PARTITION PASSWORD PLACING PLANS POSITION
! PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE
QUOTE
! RANGE READ REAL REASSIGN RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME
REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE
RIGHT ROLE ROLLBACK ROW ROWS RULE
***************
*** 483,497 **** add_typedef(char *name, char * dimension, char * length, enum ECPGttype type_enu
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP TO
TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P TRUNCATE TRUSTED TYPE_P
! UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNTIL
UPDATE USER USING
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
--- 483,497 ----
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
! TABLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIES TIME TIMESTAMP TO
TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P TRUNCATE TRUSTED TYPE_P
! UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNTIL
UPDATE USER USING
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
*** /dev/null
--- b/src/test/regress/data/accident.data
***************
*** 0 ****
--- 1,730 ----
+ 2005-01-01 3
+ 2005-01-02 4
+ 2005-01-03 0
+ 2005-01-04 3
+ 2005-01-05 0
+ 2005-01-06 5
+ 2005-01-07 5
+ 2005-01-08 6
+ 2005-01-09 7
+ 2005-01-10 0
+ 2005-01-11 10
+ 2005-01-12 1
+ 2005-01-13 1
+ 2005-01-14 5
+ 2005-01-15 1
+ 2005-01-16 2
+ 2005-01-17 8
+ 2005-01-18 7
+ 2005-01-19 3
+ 2005-01-20 7
+ 2005-01-21 0
+ 2005-01-22 0
+ 2005-01-23 1
+ 2005-01-24 2
+ 2005-01-25 1
+ 2005-01-26 4
+ 2005-01-27 3
+ 2005-01-28 8
+ 2005-01-29 0
+ 2005-01-30 2
+ 2005-01-31 10
+ 2005-02-01 7
+ 2005-02-02 6
+ 2005-02-03 6
+ 2005-02-04 4
+ 2005-02-05 4
+ 2005-02-06 10
+ 2005-02-07 1
+ 2005-02-08 9
+ 2005-02-09 9
+ 2005-02-10 7
+ 2005-02-11 1
+ 2005-02-12 7
+ 2005-02-13 3
+ 2005-02-14 7
+ 2005-02-15 6
+ 2005-02-16 5
+ 2005-02-17 5
+ 2005-02-18 0
+ 2005-02-19 2
+ 2005-02-20 5
+ 2005-02-21 0
+ 2005-02-22 5
+ 2005-02-23 3
+ 2005-02-24 8
+ 2005-02-25 3
+ 2005-02-26 5
+ 2005-02-27 5
+ 2005-02-28 4
+ 2005-03-01 8
+ 2005-03-02 1
+ 2005-03-03 10
+ 2005-03-04 2
+ 2005-03-05 9
+ 2005-03-06 3
+ 2005-03-07 7
+ 2005-03-08 0
+ 2005-03-09 0
+ 2005-03-10 7
+ 2005-03-11 2
+ 2005-03-12 0
+ 2005-03-13 2
+ 2005-03-14 7
+ 2005-03-15 3
+ 2005-03-16 7
+ 2005-03-17 3
+ 2005-03-18 1
+ 2005-03-19 7
+ 2005-03-20 7
+ 2005-03-21 1
+ 2005-03-22 2
+ 2005-03-23 5
+ 2005-03-24 7
+ 2005-03-25 6
+ 2005-03-26 5
+ 2005-03-27 0
+ 2005-03-28 5
+ 2005-03-29 6
+ 2005-03-30 5
+ 2005-03-31 8
+ 2005-04-01 3
+ 2005-04-02 5
+ 2005-04-03 7
+ 2005-04-04 10
+ 2005-04-05 0
+ 2005-04-06 10
+ 2005-04-07 6
+ 2005-04-08 6
+ 2005-04-09 2
+ 2005-04-10 0
+ 2005-04-11 0
+ 2005-04-12 6
+ 2005-04-13 0
+ 2005-04-14 1
+ 2005-04-15 7
+ 2005-04-16 7
+ 2005-04-17 0
+ 2005-04-18 9
+ 2005-04-19 8
+ 2005-04-20 10
+ 2005-04-21 10
+ 2005-04-22 1
+ 2005-04-23 4
+ 2005-04-24 1
+ 2005-04-25 6
+ 2005-04-26 0
+ 2005-04-27 4
+ 2005-04-28 10
+ 2005-04-29 4
+ 2005-04-30 5
+ 2005-05-01 5
+ 2005-05-02 4
+ 2005-05-03 4
+ 2005-05-04 6
+ 2005-05-05 1
+ 2005-05-06 6
+ 2005-05-07 6
+ 2005-05-08 2
+ 2005-05-09 8
+ 2005-05-10 6
+ 2005-05-11 4
+ 2005-05-12 3
+ 2005-05-13 7
+ 2005-05-14 5
+ 2005-05-15 5
+ 2005-05-16 10
+ 2005-05-17 6
+ 2005-05-18 9
+ 2005-05-19 7
+ 2005-05-20 7
+ 2005-05-21 1
+ 2005-05-22 5
+ 2005-05-23 10
+ 2005-05-24 7
+ 2005-05-25 0
+ 2005-05-26 1
+ 2005-05-27 3
+ 2005-05-28 4
+ 2005-05-29 7
+ 2005-05-30 3
+ 2005-05-31 6
+ 2005-06-01 0
+ 2005-06-02 10
+ 2005-06-03 2
+ 2005-06-04 3
+ 2005-06-05 3
+ 2005-06-06 9
+ 2005-06-07 9
+ 2005-06-08 7
+ 2005-06-09 3
+ 2005-06-10 8
+ 2005-06-11 10
+ 2005-06-12 4
+ 2005-06-13 3
+ 2005-06-14 8
+ 2005-06-15 8
+ 2005-06-16 8
+ 2005-06-17 2
+ 2005-06-18 7
+ 2005-06-19 3
+ 2005-06-20 8
+ 2005-06-21 1
+ 2005-06-22 3
+ 2005-06-23 1
+ 2005-06-24 9
+ 2005-06-25 10
+ 2005-06-26 0
+ 2005-06-27 9
+ 2005-06-28 2
+ 2005-06-29 0
+ 2005-06-30 8
+ 2005-07-01 9
+ 2005-07-02 6
+ 2005-07-03 5
+ 2005-07-04 1
+ 2005-07-05 4
+ 2005-07-06 7
+ 2005-07-07 1
+ 2005-07-08 0
+ 2005-07-09 3
+ 2005-07-10 6
+ 2005-07-11 7
+ 2005-07-12 8
+ 2005-07-13 6
+ 2005-07-14 8
+ 2005-07-15 3
+ 2005-07-16 9
+ 2005-07-17 7
+ 2005-07-18 4
+ 2005-07-19 4
+ 2005-07-20 7
+ 2005-07-21 9
+ 2005-07-22 0
+ 2005-07-23 5
+ 2005-07-24 0
+ 2005-07-25 3
+ 2005-07-26 1
+ 2005-07-27 7
+ 2005-07-28 10
+ 2005-07-29 2
+ 2005-07-30 1
+ 2005-07-31 8
+ 2005-08-01 1
+ 2005-08-02 4
+ 2005-08-03 9
+ 2005-08-04 8
+ 2005-08-05 3
+ 2005-08-06 9
+ 2005-08-07 8
+ 2005-08-08 0
+ 2005-08-09 5
+ 2005-08-10 9
+ 2005-08-11 1
+ 2005-08-12 1
+ 2005-08-13 6
+ 2005-08-14 6
+ 2005-08-15 10
+ 2005-08-16 0
+ 2005-08-17 5
+ 2005-08-18 8
+ 2005-08-19 3
+ 2005-08-20 6
+ 2005-08-21 4
+ 2005-08-22 1
+ 2005-08-23 9
+ 2005-08-24 6
+ 2005-08-25 4
+ 2005-08-26 0
+ 2005-08-27 9
+ 2005-08-28 6
+ 2005-08-29 2
+ 2005-08-30 4
+ 2005-08-31 0
+ 2005-09-01 10
+ 2005-09-02 0
+ 2005-09-03 10
+ 2005-09-04 10
+ 2005-09-05 6
+ 2005-09-06 7
+ 2005-09-07 0
+ 2005-09-08 1
+ 2005-09-09 7
+ 2005-09-10 5
+ 2005-09-11 0
+ 2005-09-12 4
+ 2005-09-13 0
+ 2005-09-14 7
+ 2005-09-15 10
+ 2005-09-16 3
+ 2005-09-17 2
+ 2005-09-18 3
+ 2005-09-19 8
+ 2005-09-20 0
+ 2005-09-21 5
+ 2005-09-22 8
+ 2005-09-23 6
+ 2005-09-24 1
+ 2005-09-25 9
+ 2005-09-26 0
+ 2005-09-27 2
+ 2005-09-28 8
+ 2005-09-29 5
+ 2005-09-30 7
+ 2005-10-01 3
+ 2005-10-02 0
+ 2005-10-03 3
+ 2005-10-04 8
+ 2005-10-05 6
+ 2005-10-06 4
+ 2005-10-07 6
+ 2005-10-08 3
+ 2005-10-09 9
+ 2005-10-10 6
+ 2005-10-11 2
+ 2005-10-12 5
+ 2005-10-13 6
+ 2005-10-14 7
+ 2005-10-15 7
+ 2005-10-16 5
+ 2005-10-17 1
+ 2005-10-18 0
+ 2005-10-19 5
+ 2005-10-20 10
+ 2005-10-21 4
+ 2005-10-22 5
+ 2005-10-23 1
+ 2005-10-24 6
+ 2005-10-25 2
+ 2005-10-26 9
+ 2005-10-27 9
+ 2005-10-28 2
+ 2005-10-29 8
+ 2005-10-30 7
+ 2005-10-31 2
+ 2005-11-01 4
+ 2005-11-02 10
+ 2005-11-03 9
+ 2005-11-04 3
+ 2005-11-05 2
+ 2005-11-06 5
+ 2005-11-07 1
+ 2005-11-08 2
+ 2005-11-09 1
+ 2005-11-10 8
+ 2005-11-11 6
+ 2005-11-12 2
+ 2005-11-13 2
+ 2005-11-14 1
+ 2005-11-15 8
+ 2005-11-16 2
+ 2005-11-17 7
+ 2005-11-18 10
+ 2005-11-19 10
+ 2005-11-20 9
+ 2005-11-21 1
+ 2005-11-22 8
+ 2005-11-23 1
+ 2005-11-24 1
+ 2005-11-25 2
+ 2005-11-26 3
+ 2005-11-27 5
+ 2005-11-28 1
+ 2005-11-29 0
+ 2005-11-30 0
+ 2005-12-01 8
+ 2005-12-02 6
+ 2005-12-03 0
+ 2005-12-04 6
+ 2005-12-05 6
+ 2005-12-06 1
+ 2005-12-07 8
+ 2005-12-08 6
+ 2005-12-09 2
+ 2005-12-10 0
+ 2005-12-11 10
+ 2005-12-12 10
+ 2005-12-13 0
+ 2005-12-14 9
+ 2005-12-15 1
+ 2005-12-16 3
+ 2005-12-17 0
+ 2005-12-18 4
+ 2005-12-19 4
+ 2005-12-20 10
+ 2005-12-21 5
+ 2005-12-22 9
+ 2005-12-23 2
+ 2005-12-24 2
+ 2005-12-25 5
+ 2005-12-26 5
+ 2005-12-27 6
+ 2005-12-28 2
+ 2005-12-29 1
+ 2005-12-30 2
+ 2005-12-31 8
+ 2006-01-01 0
+ 2006-01-02 0
+ 2006-01-03 1
+ 2006-01-04 7
+ 2006-01-05 2
+ 2006-01-06 5
+ 2006-01-07 10
+ 2006-01-08 5
+ 2006-01-09 8
+ 2006-01-10 8
+ 2006-01-11 6
+ 2006-01-12 4
+ 2006-01-13 2
+ 2006-01-14 9
+ 2006-01-15 0
+ 2006-01-16 7
+ 2006-01-17 5
+ 2006-01-18 6
+ 2006-01-19 8
+ 2006-01-20 0
+ 2006-01-21 4
+ 2006-01-22 10
+ 2006-01-23 2
+ 2006-01-24 1
+ 2006-01-25 9
+ 2006-01-26 0
+ 2006-01-27 7
+ 2006-01-28 4
+ 2006-01-29 1
+ 2006-01-30 5
+ 2006-01-31 0
+ 2006-02-01 3
+ 2006-02-02 8
+ 2006-02-03 7
+ 2006-02-04 4
+ 2006-02-05 9
+ 2006-02-06 7
+ 2006-02-07 0
+ 2006-02-08 8
+ 2006-02-09 0
+ 2006-02-10 8
+ 2006-02-11 2
+ 2006-02-12 5
+ 2006-02-13 9
+ 2006-02-14 4
+ 2006-02-15 7
+ 2006-02-16 1
+ 2006-02-17 5
+ 2006-02-18 6
+ 2006-02-19 2
+ 2006-02-20 4
+ 2006-02-21 8
+ 2006-02-22 1
+ 2006-02-23 2
+ 2006-02-24 3
+ 2006-02-25 9
+ 2006-02-26 2
+ 2006-02-27 2
+ 2006-02-28 0
+ 2006-03-01 7
+ 2006-03-02 0
+ 2006-03-03 6
+ 2006-03-04 2
+ 2006-03-05 10
+ 2006-03-06 10
+ 2006-03-07 1
+ 2006-03-08 1
+ 2006-03-09 8
+ 2006-03-10 0
+ 2006-03-11 0
+ 2006-03-12 6
+ 2006-03-13 6
+ 2006-03-14 3
+ 2006-03-15 7
+ 2006-03-16 6
+ 2006-03-17 7
+ 2006-03-18 10
+ 2006-03-19 0
+ 2006-03-20 4
+ 2006-03-21 9
+ 2006-03-22 5
+ 2006-03-23 2
+ 2006-03-24 3
+ 2006-03-25 3
+ 2006-03-26 3
+ 2006-03-27 7
+ 2006-03-28 8
+ 2006-03-29 7
+ 2006-03-30 9
+ 2006-03-31 0
+ 2006-04-01 6
+ 2006-04-02 1
+ 2006-04-03 4
+ 2006-04-04 4
+ 2006-04-05 4
+ 2006-04-06 5
+ 2006-04-07 1
+ 2006-04-08 0
+ 2006-04-09 7
+ 2006-04-10 8
+ 2006-04-11 8
+ 2006-04-12 6
+ 2006-04-13 7
+ 2006-04-14 10
+ 2006-04-15 10
+ 2006-04-16 10
+ 2006-04-17 2
+ 2006-04-18 0
+ 2006-04-19 3
+ 2006-04-20 0
+ 2006-04-21 10
+ 2006-04-22 1
+ 2006-04-23 7
+ 2006-04-24 10
+ 2006-04-25 3
+ 2006-04-26 10
+ 2006-04-27 3
+ 2006-04-28 0
+ 2006-04-29 5
+ 2006-04-30 6
+ 2006-05-01 0
+ 2006-05-02 4
+ 2006-05-03 8
+ 2006-05-04 9
+ 2006-05-05 3
+ 2006-05-06 1
+ 2006-05-07 8
+ 2006-05-08 7
+ 2006-05-09 7
+ 2006-05-10 1
+ 2006-05-11 2
+ 2006-05-12 8
+ 2006-05-13 9
+ 2006-05-14 1
+ 2006-05-15 2
+ 2006-05-16 3
+ 2006-05-17 10
+ 2006-05-18 0
+ 2006-05-19 6
+ 2006-05-20 10
+ 2006-05-21 6
+ 2006-05-22 4
+ 2006-05-23 3
+ 2006-05-24 1
+ 2006-05-25 1
+ 2006-05-26 4
+ 2006-05-27 8
+ 2006-05-28 7
+ 2006-05-29 1
+ 2006-05-30 5
+ 2006-05-31 0
+ 2006-06-01 1
+ 2006-06-02 9
+ 2006-06-03 6
+ 2006-06-04 7
+ 2006-06-05 1
+ 2006-06-06 9
+ 2006-06-07 1
+ 2006-06-08 0
+ 2006-06-09 1
+ 2006-06-10 0
+ 2006-06-11 5
+ 2006-06-12 9
+ 2006-06-13 2
+ 2006-06-14 9
+ 2006-06-15 9
+ 2006-06-16 2
+ 2006-06-17 1
+ 2006-06-18 6
+ 2006-06-19 0
+ 2006-06-20 0
+ 2006-06-21 9
+ 2006-06-22 2
+ 2006-06-23 1
+ 2006-06-24 6
+ 2006-06-25 4
+ 2006-06-26 4
+ 2006-06-27 9
+ 2006-06-28 7
+ 2006-06-29 3
+ 2006-06-30 1
+ 2006-07-01 10
+ 2006-07-02 5
+ 2006-07-03 8
+ 2006-07-04 5
+ 2006-07-05 2
+ 2006-07-06 5
+ 2006-07-07 10
+ 2006-07-08 5
+ 2006-07-09 2
+ 2006-07-10 2
+ 2006-07-11 8
+ 2006-07-12 7
+ 2006-07-13 6
+ 2006-07-14 8
+ 2006-07-15 9
+ 2006-07-16 5
+ 2006-07-17 5
+ 2006-07-18 6
+ 2006-07-19 8
+ 2006-07-20 0
+ 2006-07-21 8
+ 2006-07-22 9
+ 2006-07-23 5
+ 2006-07-24 0
+ 2006-07-25 4
+ 2006-07-26 0
+ 2006-07-27 5
+ 2006-07-28 1
+ 2006-07-29 9
+ 2006-07-30 0
+ 2006-07-31 4
+ 2006-08-01 8
+ 2006-08-02 3
+ 2006-08-03 10
+ 2006-08-04 5
+ 2006-08-05 7
+ 2006-08-06 1
+ 2006-08-07 7
+ 2006-08-08 8
+ 2006-08-09 5
+ 2006-08-10 0
+ 2006-08-11 9
+ 2006-08-12 6
+ 2006-08-13 7
+ 2006-08-14 8
+ 2006-08-15 0
+ 2006-08-16 5
+ 2006-08-17 0
+ 2006-08-18 5
+ 2006-08-19 4
+ 2006-08-20 0
+ 2006-08-21 1
+ 2006-08-22 0
+ 2006-08-23 1
+ 2006-08-24 4
+ 2006-08-25 2
+ 2006-08-26 4
+ 2006-08-27 10
+ 2006-08-28 10
+ 2006-08-29 1
+ 2006-08-30 6
+ 2006-08-31 10
+ 2006-09-01 4
+ 2006-09-02 5
+ 2006-09-03 9
+ 2006-09-04 9
+ 2006-09-05 0
+ 2006-09-06 1
+ 2006-09-07 0
+ 2006-09-08 0
+ 2006-09-09 5
+ 2006-09-10 9
+ 2006-09-11 5
+ 2006-09-12 8
+ 2006-09-13 7
+ 2006-09-14 6
+ 2006-09-15 4
+ 2006-09-16 1
+ 2006-09-17 1
+ 2006-09-18 1
+ 2006-09-19 0
+ 2006-09-20 0
+ 2006-09-21 4
+ 2006-09-22 3
+ 2006-09-23 9
+ 2006-09-24 9
+ 2006-09-25 1
+ 2006-09-26 5
+ 2006-09-27 3
+ 2006-09-28 2
+ 2006-09-29 4
+ 2006-09-30 0
+ 2006-10-01 7
+ 2006-10-02 9
+ 2006-10-03 7
+ 2006-10-04 4
+ 2006-10-05 3
+ 2006-10-06 5
+ 2006-10-07 7
+ 2006-10-08 1
+ 2006-10-09 7
+ 2006-10-10 6
+ 2006-10-11 2
+ 2006-10-12 9
+ 2006-10-13 5
+ 2006-10-14 8
+ 2006-10-15 7
+ 2006-10-16 8
+ 2006-10-17 9
+ 2006-10-18 9
+ 2006-10-19 5
+ 2006-10-20 6
+ 2006-10-21 3
+ 2006-10-22 7
+ 2006-10-23 10
+ 2006-10-24 9
+ 2006-10-25 5
+ 2006-10-26 9
+ 2006-10-27 2
+ 2006-10-28 0
+ 2006-10-29 5
+ 2006-10-30 9
+ 2006-10-31 10
+ 2006-11-01 5
+ 2006-11-02 4
+ 2006-11-03 1
+ 2006-11-04 2
+ 2006-11-05 0
+ 2006-11-06 1
+ 2006-11-07 8
+ 2006-11-08 2
+ 2006-11-09 1
+ 2006-11-10 7
+ 2006-11-11 9
+ 2006-11-12 7
+ 2006-11-13 5
+ 2006-11-14 1
+ 2006-11-15 1
+ 2006-11-16 8
+ 2006-11-17 4
+ 2006-11-18 0
+ 2006-11-19 10
+ 2006-11-20 7
+ 2006-11-21 4
+ 2006-11-22 4
+ 2006-11-23 10
+ 2006-11-24 6
+ 2006-11-25 2
+ 2006-11-26 9
+ 2006-11-27 2
+ 2006-11-28 3
+ 2006-11-29 5
+ 2006-11-30 5
+ 2006-12-01 0
+ 2006-12-02 4
+ 2006-12-03 8
+ 2006-12-04 0
+ 2006-12-05 9
+ 2006-12-06 10
+ 2006-12-07 8
+ 2006-12-08 10
+ 2006-12-09 5
+ 2006-12-10 7
+ 2006-12-11 5
+ 2006-12-12 9
+ 2006-12-13 4
+ 2006-12-14 0
+ 2006-12-15 0
+ 2006-12-16 8
+ 2006-12-17 10
+ 2006-12-18 10
+ 2006-12-19 1
+ 2006-12-20 3
+ 2006-12-21 8
+ 2006-12-22 1
+ 2006-12-23 9
+ 2006-12-24 10
+ 2006-12-25 9
+ 2006-12-26 0
+ 2006-12-27 3
+ 2006-12-28 7
+ 2006-12-29 1
+ 2006-12-30 1
+ 2006-12-31 5
*** /dev/null
--- b/src/test/regress/data/empsalary.data
***************
*** 0 ****
--- 1,10 ----
+ develop 10 5200 2007-08-01
+ sales 1 5000 2006-10-01
+ personnel 5 3500 2007-12-10
+ sales 4 4800 2007-08-08
+ personnel 2 3900 2006-12-23
+ develop 7 4200 2008-01-01
+ develop 9 4500 2008-01-01
+ sales 3 4800 2007-08-01
+ develop 8 6000 2006-10-01
+ develop 11 5200 2007-08-15
*** a/src/test/regress/expected/create_table.out
--- b/src/test/regress/expected/create_table.out
***************
*** 200,202 **** CREATE TABLE test_tsvector(
--- 200,212 ----
t text,
a tsvector
);
+ CREATE TABLE empsalary(
+ depname varchar,
+ empno bigint,
+ salary int,
+ enroll_date date
+ );
+ CREATE TABLE accident(
+ accident_date date,
+ people int
+ );
*** 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 ----
*** a/src/test/regress/expected/sanity_check.out
--- b/src/test/regress/expected/sanity_check.out
***************
*** 14,19 **** SELECT relname, relhasindex
--- 14,20 ----
a | f
a_star | f
abstime_tbl | f
+ accident | f
aggtest | f
array_index_op_test | t
array_op_test | f
***************
*** 41,46 **** SELECT relname, relhasindex
--- 42,48 ----
dept | f
e_star | f
emp | f
+ empsalary | f
equipment_r | f
f_star | f
fast_emp4000 | t
***************
*** 149,155 **** SELECT relname, relhasindex
timetz_tbl | f
tinterval_tbl | f
varchar_tbl | f
! (138 rows)
--
-- another sanity check: every system catalog that has OIDs should have
--- 151,157 ----
timetz_tbl | f
tinterval_tbl | f
varchar_tbl | f
! (140 rows)
--
-- another sanity check: every system catalog that has OIDs should have
*** /dev/null
--- b/src/test/regress/expected/window.out
***************
*** 0 ****
--- 1,340 ----
+ --
+ -- WINDOW FUNCTIONS
+ --
+ SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;
+ depname | empno | salary | sum
+ -----------+-------+--------+-------
+ develop | 7 | 4200 | 25100
+ develop | 9 | 4500 | 25100
+ develop | 11 | 5200 | 25100
+ develop | 10 | 5200 | 25100
+ develop | 8 | 6000 | 25100
+ personnel | 5 | 3500 | 7400
+ personnel | 2 | 3900 | 7400
+ sales | 3 | 4800 | 14600
+ sales | 4 | 4800 | 14600
+ sales | 1 | 5000 | 14600
+ (10 rows)
+
+ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary;
+ depname | empno | salary | rank
+ -----------+-------+--------+------
+ develop | 7 | 4200 | 1
+ develop | 9 | 4500 | 2
+ develop | 11 | 5200 | 3
+ develop | 10 | 5200 | 3
+ develop | 8 | 6000 | 5
+ personnel | 5 | 3500 | 1
+ personnel | 2 | 3900 | 2
+ sales | 3 | 4800 | 1
+ sales | 4 | 4800 | 1
+ sales | 1 | 5000 | 3
+ (10 rows)
+
+ SELECT y, m, SUM(SUM(people)) OVER (PARTITION BY y ORDER BY m), AVG(people) FROM(
+ SELECT EXTRACT(YEAR FROM accident_date) AS y, EXTRACT(MONTH FROM accident_date) AS m, * FROM accident)s
+ GROUP BY y, m;
+ y | m | sum | avg
+ ------+----+------+--------------------
+ 2005 | 1 | 1698 | 3.5161290322580645
+ 2005 | 2 | 1698 | 4.8928571428571429
+ 2005 | 3 | 1698 | 4.3870967741935484
+ 2005 | 4 | 1698 | 4.7333333333333333
+ 2005 | 5 | 1698 | 5.0967741935483871
+ 2005 | 6 | 1698 | 5.2666666666666667
+ 2005 | 7 | 1698 | 4.8709677419354839
+ 2005 | 8 | 1698 | 4.7419354838709677
+ 2005 | 9 | 1698 | 4.8000000000000000
+ 2005 | 10 | 1698 | 4.8709677419354839
+ 2005 | 11 | 1698 | 4.1333333333333333
+ 2005 | 12 | 1698 | 4.5483870967741935
+ 2006 | 1 | 1740 | 4.3870967741935484
+ 2006 | 2 | 1740 | 4.5000000000000000
+ 2006 | 3 | 1740 | 4.8387096774193548
+ 2006 | 4 | 1740 | 5.0333333333333333
+ 2006 | 5 | 1740 | 4.4838709677419355
+ 2006 | 6 | 1740 | 4.1333333333333333
+ 2006 | 7 | 1740 | 5.1935483870967742
+ 2006 | 8 | 1740 | 4.7419354838709677
+ 2006 | 9 | 1740 | 3.8333333333333333
+ 2006 | 10 | 1740 | 6.2258064516129032
+ 2006 | 11 | 1740 | 4.4333333333333333
+ 2006 | 12 | 1740 | 5.3225806451612903
+ (24 rows)
+
+ SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
+ depname | empno | salary | sum
+ -----------+-------+--------+-------
+ develop | 11 | 5200 | 25100
+ develop | 7 | 4200 | 25100
+ develop | 9 | 4500 | 25100
+ develop | 8 | 6000 | 25100
+ develop | 10 | 5200 | 25100
+ personnel | 5 | 3500 | 7400
+ personnel | 2 | 3900 | 7400
+ sales | 3 | 4800 | 14600
+ sales | 1 | 5000 | 14600
+ sales | 4 | 4800 | 14600
+ (10 rows)
+
+ SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w;
+ depname | empno | salary | rank
+ -----------+-------+--------+------
+ develop | 7 | 4200 | 1
+ personnel | 5 | 3500 | 1
+ sales | 3 | 4800 | 1
+ sales | 4 | 4800 | 1
+ personnel | 2 | 3900 | 2
+ develop | 9 | 4500 | 2
+ sales | 1 | 5000 | 3
+ develop | 11 | 5200 | 3
+ develop | 10 | 5200 | 3
+ develop | 8 | 6000 | 5
+ (10 rows)
+
+ 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 row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10;
+ row_number
+ ------------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ (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 dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+ dense_rank | ten | four
+ ------------+-----+------
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 2 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 2 | 7 | 1
+ 3 | 9 | 1
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ (10 rows)
+
+ SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+ percent_rank | ten | four
+ -------------------+-----+------
+ 0 | 0 | 0
+ 0 | 0 | 0
+ 1 | 4 | 0
+ 0 | 1 | 1
+ 0 | 1 | 1
+ 0.666666666666667 | 7 | 1
+ 1 | 9 | 1
+ 1 | 0 | 2
+ 0 | 1 | 3
+ 1 | 3 | 3
+ (10 rows)
+
+ SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+ cume_dist | ten | four
+ -------------------+-----+------
+ 0.333333333333333 | 0 | 0
+ 0.666666666666667 | 0 | 0
+ 1 | 4 | 0
+ 0.25 | 1 | 1
+ 0.5 | 1 | 1
+ 0.75 | 7 | 1
+ 1 | 9 | 1
+ 1 | 0 | 2
+ 0.5 | 1 | 3
+ 1 | 3 | 3
+ (10 rows)
+
+ SELECT ntile(3) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+ ntile | ten | four
+ -------+-----+------
+ 1 | 0 | 0
+ 1 | 0 | 2
+ 1 | 0 | 0
+ 1 | 1 | 1
+ 2 | 1 | 3
+ 2 | 1 | 1
+ 2 | 3 | 3
+ 3 | 4 | 0
+ 3 | 7 | 1
+ 3 | 9 | 1
+ (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)
+
+ 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)
+
+ -- empty table
+ SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;
+ count
+ -------
+ (0 rows)
+
+ -- with UNION
+ SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
+ count
+ -------
+ (0 rows)
+
+ SELECT rank() OVER (ORDER BY length('abc'));
+ rank
+ ------
+ 1
+ (1 row)
+
+ SELECT rank() OVER (ORDER BY 1);
+ rank
+ ------
+ 1
+ (1 row)
+
+ -- errors
+ SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10;
+ ERROR: window functions not allowed in WHERE clause
+ SELECT * FROM empsalary INNER JOIN accident ON row_number() OVER (ORDER BY salary) < 10;
+ ERROR: window functions not allowed in JOIN conditions
+ SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1;
+ ERROR: window functions not allowed in GROUP BY clause
+ SELECT * FROM rank() OVER (ORDER BY random());
+ ERROR: cannot use window function in function expression in FROM
+ DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random());
+ ERROR: cannot use window function in RETURNING
+ SELECT count(*) OVER (PARTITION BY four ROWS UNBOUNDED PRECEDING) FROM tenk1;
+ ERROR: ROWS/RANGE clause of window functions not yet implemented
+ SELECT count(*) OVER (PARTITION BY four RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM tenk1;
+ ERROR: ROWS/RANGE clause of window functions not yet implemented
+ SELECT count(*) OVER () FROM tenk1;
+ ERROR: either PARTITION BY or ORDER BY must be specified in window clause
+ SELECT count(*) OVER (PARTITION BY four RANGES UNBOUNDED PRECEDING) FROM tenk1;
+ ERROR: syntax error at or near "RANGES"
+ LINE 1: SELECT count(*) OVER (PARTITION BY four RANGES UNBOUNDED PRE...
+ ^
+ SELECT count(*) OVER (PARTITION BY four ROW UNBOUNDED PRECEDING) FROM tenk1;
+ ERROR: syntax error at or near "ROW"
+ LINE 1: SELECT count(*) OVER (PARTITION BY four ROW UNBOUNDED PRECED...
+ ^
+ SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
+ ERROR: syntax error at or near "ORDER"
+ LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te...
+ ^
*** a/src/test/regress/input/copy.source
--- b/src/test/regress/input/copy.source
***************
*** 41,46 **** COPY hash_f8_heap FROM '@abs_srcdir@/data/hash.data';
--- 41,50 ----
COPY test_tsvector FROM '@abs_srcdir@/data/tsearch.data';
+ COPY empsalary FROM '@abs_srcdir@/data/empsalary.data';
+
+ COPY accident FROM '@abs_srcdir@/data/accident.data';
+
-- the data in this file has a lot of duplicates in the index key
-- fields, leading to long bucket chains and lots of table expansion.
-- this is therefore a stress test of the bucket overflow code (unlike
*** a/src/test/regress/output/copy.source
--- b/src/test/regress/output/copy.source
***************
*** 22,27 **** COPY hash_name_heap FROM '@abs_srcdir@/data/hash.data';
--- 22,29 ----
COPY hash_txt_heap FROM '@abs_srcdir@/data/hash.data';
COPY hash_f8_heap FROM '@abs_srcdir@/data/hash.data';
COPY test_tsvector FROM '@abs_srcdir@/data/tsearch.data';
+ COPY empsalary FROM '@abs_srcdir@/data/empsalary.data';
+ COPY accident FROM '@abs_srcdir@/data/accident.data';
-- the data in this file has a lot of duplicates in the index key
-- fields, leading to long bucket chains and lots of table expansion.
-- this is therefore a stress test of the bucket overflow code (unlike
*** a/src/test/regress/output/misc.source
--- b/src/test/regress/output/misc.source
***************
*** 570,575 **** SELECT user_relns() AS user_relns
--- 570,576 ----
a
a_star
abstime_tbl
+ accident
aggtest
array_index_op_test
array_op_test
***************
*** 600,605 **** SELECT user_relns() AS user_relns
--- 601,607 ----
dept
e_star
emp
+ empsalary
equipment_r
f_star
fast_emp4000
***************
*** 668,674 **** SELECT user_relns() AS user_relns
toyemp
varchar_tbl
xacttest
! (101 rows)
SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
name
--- 670,676 ----
toyemp
varchar_tbl
xacttest
! (103 rows)
SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
name
*** 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/create_table.sql
--- b/src/test/regress/sql/create_table.sql
***************
*** 237,239 **** CREATE TABLE test_tsvector(
--- 237,250 ----
a tsvector
);
+ CREATE TABLE empsalary(
+ depname varchar,
+ empno bigint,
+ salary int,
+ enroll_date date
+ );
+
+ CREATE TABLE accident(
+ accident_date date,
+ people int
+ );
*** 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,86 ----
+ --
+ -- WINDOW FUNCTIONS
+ --
+
+ SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;
+
+ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary;
+
+ SELECT y, m, SUM(SUM(people)) OVER (PARTITION BY y ORDER BY m), AVG(people) FROM(
+ SELECT EXTRACT(YEAR FROM accident_date) AS y, EXTRACT(MONTH FROM accident_date) AS m, * FROM accident)s
+ GROUP BY y, m;
+
+ SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
+
+ SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w;
+
+ SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10;
+
+ SELECT row_number() OVER (ORDER BY unique2) 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 dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+ SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+ SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+ SELECT ntile(3) OVER (ORDER BY ten), 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;
+
+ 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);
+
+ -- empty table
+ SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;
+
+ -- with UNION
+ SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
+
+ SELECT rank() OVER (ORDER BY length('abc'));
+
+ SELECT rank() OVER (ORDER BY 1);
+
+ -- errors
+ SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10;
+
+ SELECT * FROM empsalary INNER JOIN accident ON row_number() OVER (ORDER BY salary) < 10;
+
+ SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1;
+
+ SELECT * FROM rank() OVER (ORDER BY random());
+
+ DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random());
+
+ SELECT count(*) OVER (PARTITION BY four ROWS UNBOUNDED PRECEDING) FROM tenk1;
+
+ SELECT count(*) OVER (PARTITION BY four RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM tenk1;
+
+ SELECT count(*) OVER () FROM tenk1;
+
+ SELECT count(*) OVER (PARTITION BY four RANGES UNBOUNDED PRECEDING) FROM tenk1;
+
+ SELECT count(*) OVER (PARTITION BY four ROW UNBOUNDED PRECEDING) FROM tenk1;
+
+ SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;