Support window functions a la SQL:2008.

Hitoshi Harada, with some kibitzing from Heikki and Tom.

10 files changed, 669 insertions, 56 deletions

diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index cdac02b71db..70688655cce 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -17,7 +17,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- *	$PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.384 2008/12/13 02:00:19 tgl Exp $
+ *	$PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.385 2008/12/28 18:53:58 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -306,6 +306,9 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
 	qry->hasAggs = pstate->p_hasAggs;
 	if (pstate->p_hasAggs)
 		parseCheckAggregates(pstate, qry);
+	qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
+	if (pstate->p_hasWindowFuncs)
+		parseCheckWindowFuncs(pstate, qry);
 
 	return qry;
 }
@@ -673,6 +676,12 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 				 errmsg("cannot use aggregate function in VALUES"),
 				 parser_errposition(pstate,
 									locate_agg_of_level((Node *) qry, 0))));
+	if (pstate->p_hasWindowFuncs)
+		ereport(ERROR,
+				(errcode(ERRCODE_WINDOWING_ERROR),
+				 errmsg("cannot use window function in VALUES"),
+				 parser_errposition(pstate,
+									locate_windowfunc((Node *) qry))));
 
 	return qry;
 }
@@ -764,6 +773,9 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 	/* make FOR UPDATE/FOR SHARE info available to addRangeTableEntry */
 	pstate->p_locking_clause = stmt->lockingClause;
 
+	/* make WINDOW info available for window functions, too */
+	pstate->p_windowdefs = stmt->windowClause;
+
 	/* process the WITH clause */
 	if (stmt->withClause)
 	{
@@ -803,7 +815,8 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 	qry->groupClause = transformGroupClause(pstate,
 											stmt->groupClause,
 											&qry->targetList,
-											qry->sortClause);
+											qry->sortClause,
+											false);
 
 	if (stmt->distinctClause == NIL)
 	{
@@ -834,6 +847,11 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 	qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
 										   "LIMIT");
 
+	/* transform window clauses after we have seen all window functions */
+	qry->windowClause = transformWindowDefinitions(pstate,
+												   pstate->p_windowdefs,
+												   &qry->targetList);
+
 	/* handle any SELECT INTO/CREATE TABLE AS spec */
 	if (stmt->intoClause)
 	{
@@ -849,6 +867,9 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 	qry->hasAggs = pstate->p_hasAggs;
 	if (pstate->p_hasAggs || qry->groupClause || qry->havingQual)
 		parseCheckAggregates(pstate, qry);
+	qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
+	if (pstate->p_hasWindowFuncs)
+		parseCheckWindowFuncs(pstate, qry);
 
 	foreach(l, stmt->lockingClause)
 	{
@@ -889,6 +910,7 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
 	Assert(stmt->whereClause == NULL);
 	Assert(stmt->groupClause == NIL);
 	Assert(stmt->havingClause == NULL);
+	Assert(stmt->windowClause == NIL);
 	Assert(stmt->op == SETOP_NONE);
 
 	/* process the WITH clause */
@@ -1061,6 +1083,12 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
 				 errmsg("cannot use aggregate function in VALUES"),
 				 parser_errposition(pstate,
 									locate_agg_of_level((Node *) newExprsLists, 0))));
+	if (pstate->p_hasWindowFuncs)
+		ereport(ERROR,
+				(errcode(ERRCODE_WINDOWING_ERROR),
+				 errmsg("cannot use window function in VALUES"),
+				 parser_errposition(pstate,
+									locate_windowfunc((Node *) newExprsLists))));
 
 	return qry;
 }
@@ -1289,6 +1317,9 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 	qry->hasAggs = pstate->p_hasAggs;
 	if (pstate->p_hasAggs || qry->groupClause || qry->havingQual)
 		parseCheckAggregates(pstate, qry);
+	qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
+	if (pstate->p_hasWindowFuncs)
+		parseCheckWindowFuncs(pstate, qry);
 
 	foreach(l, lockingClause)
 	{
@@ -1623,6 +1654,12 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
 				 errmsg("cannot use aggregate function in UPDATE"),
 				 parser_errposition(pstate,
 									locate_agg_of_level((Node *) qry, 0))));
+	if (pstate->p_hasWindowFuncs)
+		ereport(ERROR,
+				(errcode(ERRCODE_WINDOWING_ERROR),
+				 errmsg("cannot use window function in UPDATE"),
+				 parser_errposition(pstate,
+									locate_windowfunc((Node *) qry))));
 
 	/*
 	 * Now we are done with SELECT-like processing, and can get on with
@@ -1692,6 +1729,7 @@ transformReturningList(ParseState *pstate, List *returningList)
 	List	   *rlist;
 	int			save_next_resno;
 	bool		save_hasAggs;
+	bool		save_hasWindowFuncs;
 	int			length_rtable;
 
 	if (returningList == NIL)
@@ -1708,6 +1746,8 @@ transformReturningList(ParseState *pstate, List *returningList)
 	/* save other state so that we can detect disallowed stuff */
 	save_hasAggs = pstate->p_hasAggs;
 	pstate->p_hasAggs = false;
+	save_hasWindowFuncs = pstate->p_hasWindowFuncs;
+	pstate->p_hasWindowFuncs = false;
 	length_rtable = list_length(pstate->p_rtable);
 
 	/* transform RETURNING identically to a SELECT targetlist */
@@ -1722,6 +1762,12 @@ transformReturningList(ParseState *pstate, List *returningList)
 				 errmsg("cannot use aggregate function in RETURNING"),
 				 parser_errposition(pstate,
 									locate_agg_of_level((Node *) rlist, 0))));
+	if (pstate->p_hasWindowFuncs)
+		ereport(ERROR,
+				(errcode(ERRCODE_WINDOWING_ERROR),
+				 errmsg("cannot use window function in RETURNING"),
+				 parser_errposition(pstate,
+									locate_windowfunc((Node *) rlist))));
 
 	/* no new relation references please */
 	if (list_length(pstate->p_rtable) != length_rtable)
@@ -1748,6 +1794,7 @@ transformReturningList(ParseState *pstate, List *returningList)
 	/* restore state */
 	pstate->p_next_resno = save_next_resno;
 	pstate->p_hasAggs = save_hasAggs;
+	pstate->p_hasWindowFuncs = save_hasWindowFuncs;
 
 	return rlist;
 }
@@ -1883,6 +1930,10 @@ CheckSelectLocking(Query *qry)
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("SELECT FOR UPDATE/SHARE is not allowed with aggregate functions")));
+	if (qry->hasWindowFuncs)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("SELECT FOR UPDATE/SHARE is not allowed with window functions")));
 }
 
 /*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 29eab503198..59b7ada7b43 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.647 2008/12/20 16:02:55 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.648 2008/12/28 18:53:58 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -158,6 +158,7 @@ static TypeName *TableFuncTypeName(List *columns);
 	DefElem				*defelt;
 	OptionDefElem		*optdef;
 	SortBy				*sortby;
+	WindowDef			*windef;
 	JoinExpr			*jexpr;
 	IndexElem			*ielem;
 	Alias				*alias;
@@ -402,6 +403,10 @@ static TypeName *TableFuncTypeName(List *columns);
 %type <with> 	with_clause
 %type <list>	cte_list
 
+%type <list>	window_clause window_definition_list opt_partition_clause
+%type <windef>	window_definition over_clause window_specification
+%type <str>		opt_existing_window_name
+
 
 /*
  * If you make any token changes, update the keyword table in
@@ -431,8 +436,8 @@ 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
+	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
@@ -461,9 +466,9 @@ 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 OPTIONS OR
-	ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
+	ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
 
-	PARSER PARTIAL PASSWORD PLACING PLANS POSITION
+	PARSER PARTIAL PARTITION PASSWORD PLACING PLANS POSITION
 	PRECISION PRESERVE PREPARE PREPARED PRIMARY
 	PRIOR PRIVILEGES PROCEDURAL PROCEDURE
 
@@ -489,7 +494,7 @@ static TypeName *TableFuncTypeName(List *columns);
 	VACUUM VALID VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
 	VERBOSE VERSION_P VIEW VOLATILE
 
-	WHEN WHERE WHITESPACE_P WITH WITHOUT WORK WRAPPER WRITE
+	WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
 
 	XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLFOREST XMLPARSE
 	XMLPI XMLROOT XMLSERIALIZE
@@ -523,7 +528,15 @@ static TypeName *TableFuncTypeName(List *columns);
 %nonassoc	BETWEEN
 %nonassoc	IN_P
 %left		POSTFIXOP		/* dummy for postfix Op rules */
-%nonassoc	IDENT			/* to support target_el without AS */
+/*
+ * To support target_el without AS, we must give IDENT an explicit priority
+ * between POSTFIXOP and Op.  We can safely assign the same priority to
+ * various unreserved keywords as needed to resolve ambiguities (this can't
+ * have any bad effects since obviously the keywords will still behave the
+ * same as if they weren't keywords).  We need to do this for PARTITION
+ * to support opt_existing_window_name.
+ */
+%nonassoc	IDENT PARTITION
 %left		Op OPERATOR		/* multi-character ops and user-defined operators */
 %nonassoc	NOTNULL
 %nonassoc	ISNULL
@@ -1259,7 +1272,7 @@ opt_boolean:
  * - an integer or floating point number
  * - a time interval per SQL99
  * ColId gives reduce/reduce errors against ConstInterval and LOCAL,
- * so use IDENT and reject anything which is a reserved word.
+ * so use IDENT (meaning we reject anything that is a key word).
  */
 zone_value:
 			Sconst
@@ -3466,6 +3479,11 @@ old_aggr_list: old_aggr_elem						{ $$ = list_make1($1); }
 			| old_aggr_list ',' old_aggr_elem		{ $$ = lappend($1, $3); }
 		;
 
+/*
+ * Must use IDENT here to avoid reduce/reduce conflicts; fortunately none of
+ * the item names needed in old aggregate definitions are likely to become
+ * SQL keywords.
+ */
 old_aggr_elem:  IDENT '=' def_arg
 				{
 					$$ = makeDefElem($1, (Node *)$3);
@@ -6825,7 +6843,7 @@ select_clause:
 simple_select:
 			SELECT opt_distinct target_list
 			into_clause from_clause where_clause
-			group_clause having_clause
+			group_clause having_clause window_clause
 				{
 					SelectStmt *n = makeNode(SelectStmt);
 					n->distinctClause = $2;
@@ -6835,6 +6853,7 @@ simple_select:
 					n->whereClause = $6;
 					n->groupClause = $7;
 					n->havingClause = $8;
+					n->windowClause = $9;
 					$$ = (Node *)n;
 				}
 			| values_clause							{ $$ = $1; }
@@ -8076,6 +8095,7 @@ a_expr:		c_expr									{ $$ = $1; }
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @2;
 					$$ = (Node *) n;
 				}
@@ -8135,6 +8155,7 @@ a_expr:		c_expr									{ $$ = $1; }
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @4;
 					$$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "~~", $1, (Node *) n, @2);
 				}
@@ -8148,6 +8169,7 @@ a_expr:		c_expr									{ $$ = $1; }
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @5;
 					$$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "!~~", $1, (Node *) n, @2);
 				}
@@ -8161,6 +8183,7 @@ a_expr:		c_expr									{ $$ = $1; }
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @4;
 					$$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "~~*", $1, (Node *) n, @2);
 				}
@@ -8174,6 +8197,7 @@ a_expr:		c_expr									{ $$ = $1; }
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @5;
 					$$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "!~~*", $1, (Node *) n, @2);
 				}
@@ -8186,6 +8210,7 @@ a_expr:		c_expr									{ $$ = $1; }
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @2;
 					$$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "~", $1, (Node *) n, @2);
 				}
@@ -8197,6 +8222,7 @@ a_expr:		c_expr									{ $$ = $1; }
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @5;
 					$$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "~", $1, (Node *) n, @2);
 				}
@@ -8208,6 +8234,7 @@ a_expr:		c_expr									{ $$ = $1; }
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @5;
 					$$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "!~", $1, (Node *) n, @2);
 				}
@@ -8219,6 +8246,7 @@ a_expr:		c_expr									{ $$ = $1; }
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @6;
 					$$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "!~", $1, (Node *) n, @2);
 				}
@@ -8622,7 +8650,7 @@ 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 '(' ')'
+func_expr:	func_name '(' ')' over_clause
 				{
 					FuncCall *n = makeNode(FuncCall);
 					n->funcname = $1;
@@ -8630,10 +8658,11 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = $4;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
-			| func_name '(' expr_list ')'
+			| func_name '(' expr_list ')' over_clause
 				{
 					FuncCall *n = makeNode(FuncCall);
 					n->funcname = $1;
@@ -8641,10 +8670,11 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = $5;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
-			| func_name '(' VARIADIC a_expr ')'
+			| func_name '(' VARIADIC a_expr ')' over_clause
 				{
 					FuncCall *n = makeNode(FuncCall);
 					n->funcname = $1;
@@ -8652,10 +8682,11 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = TRUE;
+					n->over = $6;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
-			| func_name '(' expr_list ',' VARIADIC a_expr ')'
+			| func_name '(' expr_list ',' VARIADIC a_expr ')' over_clause
 				{
 					FuncCall *n = makeNode(FuncCall);
 					n->funcname = $1;
@@ -8663,10 +8694,11 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = TRUE;
+					n->over = $8;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
-			| func_name '(' ALL expr_list ')'
+			| func_name '(' ALL expr_list ')' over_clause
 				{
 					FuncCall *n = makeNode(FuncCall);
 					n->funcname = $1;
@@ -8678,10 +8710,11 @@ func_expr:	func_name '(' ')'
 					 * for that in FuncCall at the moment.
 					 */
 					n->func_variadic = FALSE;
+					n->over = $6;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
-			| func_name '(' DISTINCT expr_list ')'
+			| func_name '(' DISTINCT expr_list ')' over_clause
 				{
 					FuncCall *n = makeNode(FuncCall);
 					n->funcname = $1;
@@ -8689,10 +8722,11 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = TRUE;
 					n->func_variadic = FALSE;
+					n->over = $6;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
-			| func_name '(' '*' ')'
+			| func_name '(' '*' ')' over_clause
 				{
 					/*
 					 * We consider AGGREGATE(*) to invoke a parameterless
@@ -8710,6 +8744,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = TRUE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = $5;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8769,6 +8804,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8839,6 +8875,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8850,6 +8887,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8861,6 +8899,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8872,6 +8911,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8883,6 +8923,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8894,6 +8935,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8907,6 +8949,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8923,6 +8966,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8935,6 +8979,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8949,6 +8994,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8969,6 +9015,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8983,6 +9030,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -8994,6 +9042,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -9005,6 +9054,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -9016,6 +9066,7 @@ func_expr:	func_name '(' ')'
 					n->agg_star = FALSE;
 					n->agg_distinct = FALSE;
 					n->func_variadic = FALSE;
+					n->over = NULL;
 					n->location = @1;
 					$$ = (Node *)n;
 				}
@@ -9157,6 +9208,77 @@ xml_whitespace_option: PRESERVE WHITESPACE_P		{ $$ = TRUE; }
 		;
 
 /*
+ * Window Definitions
+ */
+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:
+			ColId AS window_specification
+				{
+					WindowDef *n = $3;
+					n->name = $1;
+					$$ = n;
+				}
+		;
+
+over_clause: OVER window_specification
+				{ $$ = $2; }
+			| OVER ColId
+				{
+					WindowDef *n = makeNode(WindowDef);
+					n->name = NULL;
+					n->refname = $2;
+					n->partitionClause = NIL;
+					n->orderClause = NIL;
+					n->location = @2;
+					$$ = n;
+				}
+			| /*EMPTY*/
+				{ $$ = NULL; }
+		;
+
+window_specification: '(' opt_existing_window_name opt_partition_clause
+						opt_sort_clause ')'
+				{
+					WindowDef *n = makeNode(WindowDef);
+					n->name = NULL;
+					n->refname = $2;
+					n->partitionClause = $3;
+					n->orderClause = $4;
+					n->location = @1;
+					$$ = n;
+				}
+		;
+
+/*
+ * If we see PARTITION, RANGE, or ROWS as the first token after the '('
+ * of a window_specification, we want the assumption to be that there is
+ * no existing_window_name; but those keywords are unreserved and so could
+ * be ColIds.  We fix this by making them have the same precedence as IDENT
+ * and giving the empty production here a slightly higher precedence, so
+ * that the shift/reduce conflict is resolved in favor of reducing the rule.
+ * These keywords are thus precluded from being an existing_window_name but
+ * are not reserved for any other purpose.
+ * (RANGE/ROWS are not an issue as of 8.4 for lack of frame_clause support.)
+ */
+opt_existing_window_name: ColId						{ $$ = $1; }
+			| /*EMPTY*/				%prec Op		{ $$ = NULL; }
+		;
+
+opt_partition_clause: PARTITION BY expr_list		{ $$ = $3; }
+			| /*EMPTY*/								{ $$ = NIL; }
+		;
+
+/*
  * Supporting nonterminals for expressions.
  */
 
@@ -9961,6 +10083,7 @@ unreserved_keyword:
 			| OWNER
 			| PARSER
 			| PARTIAL
+			| PARTITION
 			| PASSWORD
 			| PLANS
 			| PREPARE
@@ -10139,6 +10262,7 @@ type_func_name_keyword:
 			| NATURAL
 			| NOTNULL
 			| OUTER_P
+			| OVER
 			| OVERLAPS
 			| RIGHT
 			| SIMILAR
@@ -10229,6 +10353,7 @@ reserved_keyword:
 			| VARIADIC
 			| WHEN
 			| WHERE
+			| WINDOW
 			| WITH
 		;
 
@@ -10451,6 +10576,7 @@ makeOverlaps(List *largs, List *rargs, int location)
 	n->agg_star = FALSE;
 	n->agg_distinct = FALSE;
 	n->func_variadic = FALSE;
+	n->over = NULL;
 	n->location = location;
 	return n;
 }
diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c
index bf7b1f6ad2e..c3ad852258b 100644
--- a/src/backend/parser/keywords.c
+++ b/src/backend/parser/keywords.c
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.206 2008/12/19 16:25:17 petere Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.207 2008/12/28 18:53:58 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -287,12 +287,14 @@ const ScanKeyword ScanKeywords[] = {
 	{"order", ORDER, RESERVED_KEYWORD},
 	{"out", OUT_P, COL_NAME_KEYWORD},
 	{"outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD},
+	{"over", OVER, TYPE_FUNC_NAME_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, UNRESERVED_KEYWORD},
 	{"password", PASSWORD, UNRESERVED_KEYWORD},
 	{"placing", PLACING, RESERVED_KEYWORD},
 	{"plans", PLANS, UNRESERVED_KEYWORD},
@@ -411,6 +413,7 @@ const ScanKeyword ScanKeywords[] = {
 	{"when", WHEN, RESERVED_KEYWORD},
 	{"where", WHERE, RESERVED_KEYWORD},
 	{"whitespace", WHITESPACE_P, UNRESERVED_KEYWORD},
+	{"window", WINDOW, RESERVED_KEYWORD},
 	{"with", WITH, RESERVED_KEYWORD},
 	{"without", WITHOUT, UNRESERVED_KEYWORD},
 	{"work", WORK, UNRESERVED_KEYWORD},
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index e2645462d57..6dba470e39f 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -1,14 +1,14 @@
 /*-------------------------------------------------------------------------
  *
  * parse_agg.c
- *	  handle aggregates in parser
+ *	  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
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/parse_agg.c,v 1.84 2008/10/04 21:56:54 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/parse_agg.c,v 1.85 2008/12/28 18:53:58 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -67,7 +67,8 @@ transformAggregateCall(ParseState *pstate, Aggref *agg)
 	 */
 	if (min_varlevel == 0)
 	{
-		if (checkExprHasAggs((Node *) agg->args))
+		if (pstate->p_hasAggs &&
+			checkExprHasAggs((Node *) agg->args))
 			ereport(ERROR,
 					(errcode(ERRCODE_GROUPING_ERROR),
 					 errmsg("aggregate function calls cannot be nested"),
@@ -75,6 +76,15 @@ transformAggregateCall(ParseState *pstate, Aggref *agg)
 										locate_agg_of_level((Node *) agg->args, 0))));
 	}
 
+	/* It can't contain window functions either */
+	if (pstate->p_hasWindowFuncs &&
+		checkExprHasWindowFuncs((Node *) agg->args))
+		ereport(ERROR,
+				(errcode(ERRCODE_GROUPING_ERROR),
+				 errmsg("aggregate function calls cannot contain window function calls"),
+				 parser_errposition(pstate,
+									locate_windowfunc((Node *) agg->args))));
+
 	if (min_varlevel < 0)
 		min_varlevel = 0;
 	agg->agglevelsup = min_varlevel;
@@ -85,6 +95,98 @@ transformAggregateCall(ParseState *pstate, Aggref *agg)
 	pstate->p_hasAggs = true;
 }
 
+/*
+ * transformWindowFuncCall -
+ *		Finish initial transformation of a window function call
+ *
+ * parse_func.c has recognized the function as a window function, and has set
+ * up all the fields of the WindowFunc except winref.  Here we must (1) add
+ * the WindowDef to the pstate (if not a duplicate of one already present) and
+ * set winref to link to it; and (2) mark p_hasWindowFuncs true in the pstate.
+ * Unlike aggregates, only the most closely nested pstate level need be
+ * considered --- there are no "outer window functions" per SQL spec.
+ */
+void
+transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
+						WindowDef *windef)
+{
+	/*
+	 * A window function call can't contain another one (but aggs are OK).
+	 * XXX is this required by spec, or just an unimplemented feature?
+	 */
+	if (pstate->p_hasWindowFuncs &&
+		checkExprHasWindowFuncs((Node *) wfunc->args))
+		ereport(ERROR,
+				(errcode(ERRCODE_WINDOWING_ERROR),
+				 errmsg("window function calls cannot be nested"),
+				 parser_errposition(pstate,
+									locate_windowfunc((Node *) wfunc->args))));
+
+	/*
+	 * If the OVER clause just specifies a reference name, find that
+	 * WINDOW clause (which had better be present).  Otherwise, try to
+	 * match all the properties of the OVER clause, and make a new entry
+	 * in the p_windowdefs list if no luck.
+	 */
+	Assert(!windef->name);
+	if (windef->refname &&
+		windef->partitionClause == NIL &&
+		windef->orderClause == NIL)
+	{
+		Index		winref = 0;
+		ListCell   *lc;
+
+		foreach(lc, pstate->p_windowdefs)
+		{
+			WindowDef *refwin = (WindowDef *) lfirst(lc);
+
+			winref++;
+			if (refwin->name && strcmp(refwin->name, windef->refname) == 0)
+			{
+				wfunc->winref = winref;
+				break;
+			}
+		}
+		if (lc == NULL)			/* didn't find it? */
+			ereport(ERROR,
+					(errcode(ERRCODE_UNDEFINED_OBJECT),
+					 errmsg("window \"%s\" does not exist", windef->refname),
+					 parser_errposition(pstate, windef->location)));
+	}
+	else
+	{
+		Index		winref = 0;
+		ListCell   *lc;
+
+		foreach(lc, pstate->p_windowdefs)
+		{
+			WindowDef *refwin = (WindowDef *) lfirst(lc);
+
+			winref++;
+			if (refwin->refname && windef->refname &&
+				strcmp(refwin->name, windef->refname) == 0)
+				/* matched on refname */ ;
+			else if (!refwin->refname && !windef->refname)
+				/* matched, no refname */ ;
+			else
+				continue;
+			if (equal(refwin->partitionClause, windef->partitionClause) &&
+				equal(refwin->orderClause, windef->orderClause))
+			{
+				/* found a duplicate window specification */
+				wfunc->winref = winref;
+				break;
+			}
+		}
+		if (lc == NULL)			/* didn't find it? */
+		{
+			pstate->p_windowdefs = lappend(pstate->p_windowdefs, windef);
+			wfunc->winref = list_length(pstate->p_windowdefs);
+		}
+	}
+
+	pstate->p_hasWindowFuncs = true;
+}
 
 /*
  * parseCheckAggregates
@@ -207,6 +309,11 @@ parseCheckAggregates(ParseState *pstate, Query *qry)
 
 	/*
 	 * Check the targetlist and HAVING clause for ungrouped variables.
+	 *
+	 * Note: because we check resjunk tlist elements as well as regular ones,
+	 * this will also find ungrouped variables that came from ORDER BY and
+	 * WINDOW clauses.  For that matter, it's also going to examine the
+	 * grouping expressions themselves --- but they'll all pass the test ...
 	 */
 	clause = (Node *) qry->targetList;
 	if (hasJoinRTEs)
@@ -226,11 +333,94 @@ parseCheckAggregates(ParseState *pstate, Query *qry)
 	if (pstate->p_hasAggs && hasSelfRefRTEs)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_RECURSION),
-				 errmsg("aggregates not allowed in a recursive query's recursive term"),
+				 errmsg("aggregate functions not allowed in a recursive query's recursive term"),
 				 parser_errposition(pstate,
 									locate_agg_of_level((Node *) qry, 0))));
 }
 
+/*
+ * parseCheckWindowFuncs
+ *	Check for window functions where they shouldn't be.
+ *
+ *	We have to forbid window functions in WHERE, JOIN/ON, HAVING, GROUP BY,
+ *	and window specifications.  (Other clauses, such as RETURNING and LIMIT,
+ *	have already been checked.)  Transformation of all these clauses must
+ *	be completed already.
+ */
+void
+parseCheckWindowFuncs(ParseState *pstate, Query *qry)
+{
+	ListCell	   *l;
+
+	/* This should only be called if we found window functions */
+	Assert(pstate->p_hasWindowFuncs);
+
+	if (checkExprHasWindowFuncs(qry->jointree->quals))
+		ereport(ERROR,
+				(errcode(ERRCODE_WINDOWING_ERROR),
+				 errmsg("window functions not allowed in WHERE clause"),
+				 parser_errposition(pstate,
+									locate_windowfunc(qry->jointree->quals))));
+	if (checkExprHasWindowFuncs((Node *) qry->jointree->fromlist))
+		ereport(ERROR,
+				(errcode(ERRCODE_WINDOWING_ERROR),
+				 errmsg("window functions not allowed in JOIN conditions"),
+				 parser_errposition(pstate,
+									locate_windowfunc((Node *) qry->jointree->fromlist))));
+	if (checkExprHasWindowFuncs(qry->havingQual))
+		ereport(ERROR,
+				(errcode(ERRCODE_WINDOWING_ERROR),
+				 errmsg("window functions not allowed in HAVING clause"),
+				 parser_errposition(pstate,
+									locate_windowfunc(qry->havingQual))));
+
+	foreach(l, qry->groupClause)
+	{
+		SortGroupClause *grpcl = (SortGroupClause *) lfirst(l);
+		Node	   *expr;
+
+		expr = get_sortgroupclause_expr(grpcl, qry->targetList);
+		if (checkExprHasWindowFuncs(expr))
+			ereport(ERROR,
+					(errcode(ERRCODE_WINDOWING_ERROR),
+					 errmsg("window functions not allowed in GROUP BY clause"),
+					 parser_errposition(pstate,
+										locate_windowfunc(expr))));
+	}
+
+	foreach(l, qry->windowClause)
+	{
+		WindowClause   *wc = (WindowClause *) lfirst(l);
+		ListCell   *l2;
+
+		foreach(l2, wc->partitionClause)
+		{
+			SortGroupClause *grpcl = (SortGroupClause *) lfirst(l2);
+			Node	   *expr;
+
+			expr = get_sortgroupclause_expr(grpcl, qry->targetList);
+			if (checkExprHasWindowFuncs(expr))
+				ereport(ERROR,
+						(errcode(ERRCODE_WINDOWING_ERROR),
+						 errmsg("window functions not allowed in window definition"),
+						 parser_errposition(pstate,
+											locate_windowfunc(expr))));
+		}
+		foreach(l2, wc->orderClause)
+		{
+			SortGroupClause *grpcl = (SortGroupClause *) lfirst(l2);
+			Node	   *expr;
+
+			expr = get_sortgroupclause_expr(grpcl, qry->targetList);
+			if (checkExprHasWindowFuncs(expr))
+				ereport(ERROR,
+						(errcode(ERRCODE_WINDOWING_ERROR),
+						 errmsg("window functions not allowed in window definition"),
+						 parser_errposition(pstate,
+											locate_windowfunc(expr))));
+		}
+	}
+}
 
 /*
  * check_ungrouped_columns -
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 0e5fbfd28ac..df30361f0a5 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.181 2008/10/06 02:12:56 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.182 2008/12/28 18:53:58 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -40,8 +40,14 @@
 #define ORDER_CLAUSE 0
 #define GROUP_CLAUSE 1
 #define DISTINCT_ON_CLAUSE 2
+#define PARTITION_CLAUSE 3
 
-static char *clauseText[] = {"ORDER BY", "GROUP BY", "DISTINCT ON"};
+static const char * const clauseText[] = {
+	"ORDER BY",
+	"GROUP BY",
+	"DISTINCT ON",
+	"PARTITION BY"
+};
 
 static void extractRemainingColumns(List *common_colnames,
 						List *src_colnames, List *src_colvars,
@@ -76,6 +82,7 @@ static List *addTargetToSortList(ParseState *pstate, TargetEntry *tle,
 static List *addTargetToGroupList(ParseState *pstate, TargetEntry *tle,
 					 List *grouplist, List *targetlist, int location,
 					 bool resolveUnknown);
+static WindowClause *findWindowClause(List *wclist, const char *name);
 
 
 /*
@@ -555,15 +562,20 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
 	 * Disallow aggregate functions in the expression.	(No reason to postpone
 	 * this check until parseCheckAggregates.)
 	 */
-	if (pstate->p_hasAggs)
-	{
-		if (checkExprHasAggs(funcexpr))
-			ereport(ERROR,
-					(errcode(ERRCODE_GROUPING_ERROR),
-					 errmsg("cannot use aggregate function in function expression in FROM"),
-					 parser_errposition(pstate,
-										locate_agg_of_level(funcexpr, 0))));
-	}
+	if (pstate->p_hasAggs &&
+		checkExprHasAggs(funcexpr))
+		ereport(ERROR,
+				(errcode(ERRCODE_GROUPING_ERROR),
+				 errmsg("cannot use aggregate function in function expression in FROM"),
+				 parser_errposition(pstate,
+									locate_agg_of_level(funcexpr, 0))));
+	if (pstate->p_hasWindowFuncs &&
+		checkExprHasWindowFuncs(funcexpr))
+		ereport(ERROR,
+				(errcode(ERRCODE_WINDOWING_ERROR),
+				 errmsg("cannot use window function in function expression in FROM"),
+				 parser_errposition(pstate,
+									locate_windowfunc(funcexpr))));
 
 	/*
 	 * OK, build an RTE for the function.
@@ -1156,16 +1168,28 @@ transformLimitClause(ParseState *pstate, Node *clause,
 				 parser_errposition(pstate,
 									locate_var_of_level(qual, 0))));
 	}
-	if (checkExprHasAggs(qual))
+	if (pstate->p_hasAggs &&
+		checkExprHasAggs(qual))
 	{
 		ereport(ERROR,
 				(errcode(ERRCODE_GROUPING_ERROR),
 		/* translator: %s is name of a SQL construct, eg LIMIT */
-				 errmsg("argument of %s must not contain aggregates",
+				 errmsg("argument of %s must not contain aggregate functions",
 						constructName),
 				 parser_errposition(pstate,
 									locate_agg_of_level(qual, 0))));
 	}
+	if (pstate->p_hasWindowFuncs &&
+		checkExprHasWindowFuncs(qual))
+	{
+		ereport(ERROR,
+				(errcode(ERRCODE_WINDOWING_ERROR),
+		/* translator: %s is name of a SQL construct, eg LIMIT */
+				 errmsg("argument of %s must not contain window functions",
+						constructName),
+				 parser_errposition(pstate,
+									locate_windowfunc(qual))));
+	}
 
 	return qual;
 }
@@ -1234,7 +1258,7 @@ findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause)
 		char	   *name = strVal(linitial(((ColumnRef *) node)->fields));
 		int			location = ((ColumnRef *) node)->location;
 
-		if (clause == GROUP_CLAUSE)
+		if (clause == GROUP_CLAUSE || clause == PARTITION_CLAUSE)
 		{
 			/*
 			 * In GROUP BY, we must prefer a match against a FROM-clause
@@ -1251,6 +1275,8 @@ findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause)
 			 * SQL99 do not allow GROUPing BY an outer reference, so this
 			 * breaks no cases that are legal per spec, and it seems a more
 			 * self-consistent behavior.
+			 *
+			 * Window PARTITION BY clauses should act exactly like GROUP BY.
 			 */
 			if (colNameToVar(pstate, name, true, location) != NULL)
 				name = NULL;
@@ -1356,12 +1382,17 @@ findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause)
  *
  * GROUP BY items will be added to the targetlist (as resjunk columns)
  * if not already present, so the targetlist must be passed by reference.
+ *
+ * This is also used for window PARTITION BY clauses (which actually act
+ * just the same, except for the clause name used in error messages).
  */
 List *
 transformGroupClause(ParseState *pstate, List *grouplist,
-					 List **targetlist, List *sortClause)
+					 List **targetlist, List *sortClause,
+					 bool isPartition)
 {
 	List	   *result = NIL;
+	int			clause = isPartition ? PARTITION_CLAUSE : GROUP_CLAUSE;
 	ListCell   *gl;
 
 	foreach(gl, grouplist)
@@ -1370,8 +1401,7 @@ transformGroupClause(ParseState *pstate, List *grouplist,
 		TargetEntry *tle;
 		bool		found = false;
 
-		tle = findTargetlistEntry(pstate, gexpr,
-								  targetlist, GROUP_CLAUSE);
+		tle = findTargetlistEntry(pstate, gexpr, targetlist, clause);
 
 		/* Eliminate duplicates (GROUP BY x, x) */
 		if (targetIsInSortList(tle, InvalidOid, result))
@@ -1452,6 +1482,125 @@ transformSortClause(ParseState *pstate,
 }
 
 /*
+ * transformWindowDefinitions -
+ *		transform window definitions (WindowDef to WindowClause)
+ */
+List *
+transformWindowDefinitions(ParseState *pstate,
+						   List *windowdefs,
+						   List **targetlist)
+{
+	List	   *result = NIL;
+	Index		winref = 0;
+	ListCell   *lc;
+
+	foreach(lc, windowdefs)
+	{
+		WindowDef	 *windef = (WindowDef *) lfirst(lc);
+		WindowClause *refwc = NULL;
+		List		 *partitionClause;
+		List		 *orderClause;
+		WindowClause *wc;
+
+		winref++;
+
+		/*
+		 * Check for duplicate window names.
+		 */
+		if (windef->name &&
+			findWindowClause(result, windef->name) != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_WINDOWING_ERROR),
+					 errmsg("window \"%s\" is already defined", windef->name),
+					 parser_errposition(pstate, windef->location)));
+
+		/*
+		 * If it references a previous window, look that up.
+		 */
+		if (windef->refname)
+		{
+			refwc = findWindowClause(result, windef->refname);
+			if (refwc == NULL)
+				ereport(ERROR,
+						(errcode(ERRCODE_UNDEFINED_OBJECT),
+						 errmsg("window \"%s\" does not exist",
+								windef->refname),
+						 parser_errposition(pstate, windef->location)));
+		}
+
+		/*
+		 * Transform PARTITION and ORDER specs, if any.  These are treated
+		 * exactly like top-level GROUP BY and ORDER BY clauses, including
+		 * the special handling of nondefault operator semantics.
+		 */
+		orderClause = transformSortClause(pstate,
+										  windef->orderClause,
+										  targetlist,
+										  true);
+		partitionClause = transformGroupClause(pstate,
+											   windef->partitionClause,
+											   targetlist,
+											   orderClause,
+											   true);
+
+		/*
+		 * And prepare the new WindowClause.
+		 */
+		wc = makeNode(WindowClause);
+		wc->name = windef->name;
+		wc->refname = windef->refname;
+
+		/*
+		 * Per spec, a windowdef that references a previous one copies the
+		 * previous partition clause (and mustn't specify its own).  It can
+		 * specify its own ordering clause. but only if the previous one
+		 * had none.
+		 */
+		if (refwc)
+		{
+			if (partitionClause)
+				ereport(ERROR,
+						(errcode(ERRCODE_WINDOWING_ERROR),
+						 errmsg("cannot override PARTITION BY clause of window \"%s\"",
+								windef->refname),
+						 parser_errposition(pstate, windef->location)));
+			wc->partitionClause = copyObject(refwc->partitionClause);
+		}
+		else
+			wc->partitionClause = partitionClause;
+		if (refwc)
+		{
+			if (orderClause && refwc->orderClause)
+				ereport(ERROR,
+						(errcode(ERRCODE_WINDOWING_ERROR),
+						 errmsg("cannot override ORDER BY clause of window \"%s\"",
+								windef->refname),
+						 parser_errposition(pstate, windef->location)));
+			if (orderClause)
+			{
+				wc->orderClause = orderClause;
+				wc->copiedOrder = false;
+			}
+			else
+			{
+				wc->orderClause = copyObject(refwc->orderClause);
+				wc->copiedOrder = true;
+			}
+		}
+		else
+		{
+			wc->orderClause = orderClause;
+			wc->copiedOrder = false;
+		}
+		wc->winref = winref;
+
+		result = lappend(result, wc);
+	}
+
+	return result;
+}
+
+/*
  * transformDistinctClause -
  *	  transform a DISTINCT clause
  *
@@ -1919,3 +2068,23 @@ targetIsInSortList(TargetEntry *tle, Oid sortop, List *sortList)
 	}
 	return false;
 }
+
+/*
+ * findWindowClause
+ *		Find the named WindowClause in the list, or return NULL if not there
+ */
+static WindowClause *
+findWindowClause(List *wclist, const char *name)
+{
+	ListCell   *l;
+
+	foreach(l, wclist)
+	{
+		WindowClause *wc = (WindowClause *) lfirst(l);
+
+		if (wc->name && strcmp(wc->name, name) == 0)
+			return wc;
+	}
+
+	return NULL;
+}
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 1bac7ca2fce..29e3a9be017 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/parse_coerce.c,v 2.172 2008/12/14 19:45:52 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/parse_coerce.c,v 2.173 2008/12/28 18:53:58 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -746,6 +746,7 @@ build_coercion_expression(Node *node,
 		/* Assert(targetTypeId == procstruct->prorettype); */
 		Assert(!procstruct->proretset);
 		Assert(!procstruct->proisagg);
+		Assert(!procstruct->proiswindow);
 		nargs = procstruct->pronargs;
 		Assert(nargs >= 1 && nargs <= 3);
 		/* Assert(procstruct->proargtypes.values[0] == exprType(node)); */
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index b5299d010a6..c14970d4561 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/parse_expr.c,v 1.237 2008/10/26 02:46:25 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/parse_expr.c,v 1.238 2008/12/28 18:53:58 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -286,6 +286,7 @@ transformExpr(ParseState *pstate, Node *expr)
 		case T_Const:
 		case T_Param:
 		case T_Aggref:
+		case T_WindowFunc:
 		case T_ArrayRef:
 		case T_FuncExpr:
 		case T_OpExpr:
@@ -361,7 +362,7 @@ transformIndirection(ParseState *pstate, Node *basenode, List *indirection)
 									   list_make1(n),
 									   list_make1(result),
 									   false, false, false,
-									   true, -1);
+									   NULL, true, -1);
 		}
 	}
 	/* process trailing subscripts, if any */
@@ -505,7 +506,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
 											 list_make1(makeString(name2)),
 											 list_make1(node),
 											 false, false, false,
-											 true, cref->location);
+											 NULL, true, cref->location);
 				}
 				break;
 			}
@@ -546,7 +547,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
 											 list_make1(makeString(name3)),
 											 list_make1(node),
 											 false, false, false,
-											 true, cref->location);
+											 NULL, true, cref->location);
 				}
 				break;
 			}
@@ -601,7 +602,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
 											 list_make1(makeString(name4)),
 											 list_make1(node),
 											 false, false, false,
-											 true, cref->location);
+											 NULL, true, cref->location);
 				}
 				break;
 			}
@@ -1108,6 +1109,7 @@ transformFuncCall(ParseState *pstate, FuncCall *fn)
 							 fn->agg_star,
 							 fn->agg_distinct,
 							 fn->func_variadic,
+							 fn->over,
 							 false,
 							 fn->location);
 }
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index d0b74ff5d96..b48dd11495f 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/parse_func.c,v 1.209 2008/12/18 18:20:34 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/parse_func.c,v 1.210 2008/12/28 18:53:58 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -63,7 +63,7 @@ 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)
+				  WindowDef *over, bool is_column, int location)
 {
 	Oid			rettype;
 	Oid			funcid;
@@ -131,8 +131,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 	 * the "function call" could be a projection.  We also check that there
 	 * wasn't any aggregate or variadic decoration.
 	 */
-	if (nargs == 1 && !agg_star && !agg_distinct && !func_variadic &&
-		list_length(funcname) == 1)
+	if (nargs == 1 && !agg_star && !agg_distinct && over == NULL &&
+		!func_variadic && list_length(funcname) == 1)
 	{
 		Oid			argtype = actual_arg_types[0];
 
@@ -196,8 +196,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 			errmsg("DISTINCT specified, but %s is not an aggregate function",
 				   NameListToString(funcname)),
 					 parser_errposition(pstate, location)));
+		if (over)
+			ereport(ERROR,
+					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+					 errmsg("OVER specified, but %s is not a window function nor an aggregate function",
+					 		NameListToString(funcname)),
+					 parser_errposition(pstate, location)));
 	}
-	else if (fdresult != FUNCDETAIL_AGGREGATE)
+	else if (!(fdresult == FUNCDETAIL_AGGREGATE ||
+			   fdresult == FUNCDETAIL_WINDOWFUNC))
 	{
 		/*
 		 * Oops.  Time to die.
@@ -317,7 +324,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 
 		retval = (Node *) funcexpr;
 	}
-	else
+	else if (fdresult == FUNCDETAIL_AGGREGATE && !over)
 	{
 		/* aggregate function */
 		Aggref	   *aggref = makeNode(Aggref);
@@ -340,16 +347,69 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 							NameListToString(funcname)),
 					 parser_errposition(pstate, location)));
 
+		if (retset)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
+					 errmsg("aggregates cannot return sets"),
+					 parser_errposition(pstate, location)));
+
 		/* parse_agg.c does additional aggregate-specific processing */
 		transformAggregateCall(pstate, aggref);
 
 		retval = (Node *) aggref;
+	}
+	else
+	{
+		/* window function */
+		WindowFunc *wfunc = makeNode(WindowFunc);
+
+		/*
+		 * True window functions must be called with a window definition.
+		 */
+		if (!over)
+			ereport(ERROR,
+					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+					 errmsg("window function call requires an OVER clause"),
+					 parser_errposition(pstate, location)));
+
+		wfunc->winfnoid = funcid;
+		wfunc->wintype = rettype;
+		wfunc->args = fargs;
+		/* winref will be set by transformWindowFuncCall */
+		wfunc->winstar = agg_star;
+		wfunc->winagg = (fdresult == FUNCDETAIL_AGGREGATE);
+		wfunc->location = location;
+
+		/*
+		 * agg_star is allowed for aggregate functions but distinct isn't
+		 */
+		if (agg_distinct)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("DISTINCT is not implemented for window functions"),
+					 parser_errposition(pstate, location)));
+
+		/*
+		 * Reject attempt to call a parameterless aggregate without (*)
+		 * syntax.	This is mere pedantry but some folks insisted ...
+		 */
+		if (wfunc->winagg && 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)));
 
 		if (retset)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
-					 errmsg("aggregates cannot return sets"),
+					 errmsg("window functions cannot return sets"),
 					 parser_errposition(pstate, location)));
+
+		/* parse_agg.c does additional window-func-specific processing */
+		transformWindowFuncCall(pstate, wfunc, over);
+
+		retval = (Node *) wfunc;
 	}
 
 	return retval;
@@ -948,7 +1008,12 @@ func_get_detail(List *funcname,
 			else
 				*argdefaults = NIL;
 		}
-		result = pform->proisagg ? FUNCDETAIL_AGGREGATE : FUNCDETAIL_NORMAL;
+		if (pform->proisagg)
+			result = FUNCDETAIL_AGGREGATE;
+		else if (pform->proiswindow)
+			result = FUNCDETAIL_WINDOWFUNC;
+		else
+			result = FUNCDETAIL_NORMAL;
 		ReleaseSysCache(ftup);
 		return result;
 	}
diff --git a/src/backend/parser/parse_type.c b/src/backend/parser/parse_type.c
index 0253cfe1593..e7c43daf7f2 100644
--- a/src/backend/parser/parse_type.c
+++ b/src/backend/parser/parse_type.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/parse_type.c,v 1.100 2008/10/04 21:56:54 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/parse_type.c,v 1.101 2008/12/28 18:53:59 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -611,6 +611,7 @@ parseTypeString(const char *str, Oid *type_id, int32 *typmod_p)
 		stmt->whereClause != NULL ||
 		stmt->groupClause != NIL ||
 		stmt->havingClause != NULL ||
+		stmt->windowClause != NIL ||
 		stmt->withClause != NULL ||
 		stmt->valuesLists != NIL ||
 		stmt->sortClause != NIL ||
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index bb3a9142d6f..739f1b03a02 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -19,7 +19,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- *	$PostgreSQL: pgsql/src/backend/parser/parse_utilcmd.c,v 2.18 2008/12/06 23:22:46 momjian Exp $
+ *	$PostgreSQL: pgsql/src/backend/parser/parse_utilcmd.c,v 2.19 2008/12/28 18:53:59 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -391,6 +391,7 @@ transformColumnDefinition(ParseState *pstate, CreateStmtContext *cxt,
 		funccallnode->agg_star = false;
 		funccallnode->agg_distinct = false;
 		funccallnode->func_variadic = false;
+		funccallnode->over = NULL;
 		funccallnode->location = -1;
 
 		constraint = makeNode(Constraint);
@@ -1471,6 +1472,10 @@ transformRuleStmt(RuleStmt *stmt, const char *queryString,
 		ereport(ERROR,
 				(errcode(ERRCODE_GROUPING_ERROR),
 		   errmsg("cannot use aggregate function in rule WHERE condition")));
+	if (pstate->p_hasWindowFuncs)
+		ereport(ERROR,
+				(errcode(ERRCODE_WINDOWING_ERROR),
+				 errmsg("cannot use window function in rule WHERE condition")));
 
 	/*
 	 * 'instead nothing' rules with a qualification need a query rangetable so