toplevel-dml-cte.20101005.diff

diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index c87f35c..45a1643 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -21,6 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
+[ WITH [ RECURSIVE ] with_query ]
 DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
     [ USING <replaceable class="PARAMETER">using_list</replaceable> ]
     [ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
@@ -84,6 +85,18 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ]
 
   <variablelist>
    <varlistentry>
+    <term><replaceable class="PARAMETER">with_query</replaceable></term>
+    <listitem>
+     <para>
+      The <literal>WITH</literal> clause allows you to specify one or more
+      subqueries that can be referenced by name in the primary query.
+      See <xref linkend="queries-with"> and <xref linkend="sql-select">
+      for details.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><literal>ONLY</></term>
     <listitem>
      <para>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 6d17ef0..73685b9 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -21,6 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
+[ WITH [ RECURSIVE ] with_query ]
 INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
     { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
     [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
@@ -85,6 +86,24 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
 
   <variablelist>
    <varlistentry>
+    <term><replaceable class="PARAMETER">with_query</replaceable></term>
+    <listitem>
+     <para>
+      The <literal>WITH</literal> clause allows you to specify one or more
+      subqueries that can be referenced by name in the primary query.
+      See <xref linkend="queries-with"> and <xref linkend="sql-select">
+      for details.
+     </para>
+     <para>
+      It is possible that <literal>SELECT</literal> query also has
+      <literal>WITH</literal>.  In this case the two
+      <replaceable>with_query</replaceable> can be referred from
+      the <literal>SELECT</literal> query.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><replaceable class="PARAMETER">table</replaceable></term>
     <listitem>
      <para>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index c897634..08fb9a0 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -21,6 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
+[ WITH [ RECURSIVE ] with_query ]
 UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
     SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
           ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
@@ -80,6 +81,18 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
 
   <variablelist>
    <varlistentry>
+    <term><replaceable class="PARAMETER">with_query</replaceable></term>
+    <listitem>
+     <para>
+      The <literal>WITH</literal> clause allows you to specify one or more
+      subqueries that can be referenced by name in the primary query.
+      See <xref linkend="queries-with"> and <xref linkend="sql-select">
+      for details.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><replaceable class="PARAMETER">table</replaceable></term>
     <listitem>
      <para>
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 5bd0ef0..18b0b90 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2288,6 +2288,7 @@ _copyInsertStmt(InsertStmt *from)
 	COPY_NODE_FIELD(cols);
 	COPY_NODE_FIELD(selectStmt);
 	COPY_NODE_FIELD(returningList);
+	COPY_NODE_FIELD(withClause);
 
 	return newnode;
 }
@@ -2301,6 +2302,7 @@ _copyDeleteStmt(DeleteStmt *from)
 	COPY_NODE_FIELD(usingClause);
 	COPY_NODE_FIELD(whereClause);
 	COPY_NODE_FIELD(returningList);
+	COPY_NODE_FIELD(withClause);
 
 	return newnode;
 }
@@ -2315,6 +2317,7 @@ _copyUpdateStmt(UpdateStmt *from)
 	COPY_NODE_FIELD(whereClause);
 	COPY_NODE_FIELD(fromClause);
 	COPY_NODE_FIELD(returningList);
+	COPY_NODE_FIELD(withClause);
 
 	return newnode;
 }
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index c7dd42d..0ea3a31 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -890,6 +890,7 @@ _equalInsertStmt(InsertStmt *a, InsertStmt *b)
 	COMPARE_NODE_FIELD(cols);
 	COMPARE_NODE_FIELD(selectStmt);
 	COMPARE_NODE_FIELD(returningList);
+	COMPARE_NODE_FIELD(withClause);
 
 	return true;
 }
@@ -901,6 +902,7 @@ _equalDeleteStmt(DeleteStmt *a, DeleteStmt *b)
 	COMPARE_NODE_FIELD(usingClause);
 	COMPARE_NODE_FIELD(whereClause);
 	COMPARE_NODE_FIELD(returningList);
+	COMPARE_NODE_FIELD(withClause);
 
 	return true;
 }
@@ -913,6 +915,7 @@ _equalUpdateStmt(UpdateStmt *a, UpdateStmt *b)
 	COMPARE_NODE_FIELD(whereClause);
 	COMPARE_NODE_FIELD(fromClause);
 	COMPARE_NODE_FIELD(returningList);
+	COMPARE_NODE_FIELD(withClause);
 
 	return true;
 }
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 21342e8..af7be94 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -283,6 +283,13 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
 
 	qry->commandType = CMD_DELETE;
 
+	/* process the WITH clause independently of all else */
+	if (stmt->withClause)
+	{
+		qry->hasRecursive = stmt->withClause->recursive;
+		qry->cteList = transformWithClause(pstate, stmt->withClause);
+	}
+
 	/* set up range table with just the result rel */
 	qry->resultRelation = setTargetTable(pstate, stmt->relation,
 								  interpretInhOption(stmt->relation->inhOpt),
@@ -343,6 +350,13 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 	qry->commandType = CMD_INSERT;
 	pstate->p_is_insert = true;
 
+	/* process the WITH clause independently of all else */
+	if (stmt->withClause)
+	{
+		qry->hasRecursive = stmt->withClause->recursive;
+		qry->cteList = transformWithClause(pstate, stmt->withClause);
+	}
+
 	/*
 	 * We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
 	 * VALUES list, or general SELECT input.  We special-case VALUES, both for
@@ -376,8 +390,6 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 		pstate->p_relnamespace = NIL;
 		sub_varnamespace = pstate->p_varnamespace;
 		pstate->p_varnamespace = NIL;
-		/* There can't be any outer WITH to worry about */
-		Assert(pstate->p_ctenamespace == NIL);
 	}
 	else
 	{
@@ -518,13 +530,6 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 		List	   *exprsLists = NIL;
 		int			sublist_length = -1;
 
-		/* process the WITH clause */
-		if (selectStmt->withClause)
-		{
-			qry->hasRecursive = selectStmt->withClause->recursive;
-			qry->cteList = transformWithClause(pstate, selectStmt->withClause);
-		}
-
 		foreach(lc, selectStmt->valuesLists)
 		{
 			List	   *sublist = (List *) lfirst(lc);
@@ -618,13 +623,6 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 
 		Assert(list_length(valuesLists) == 1);
 
-		/* process the WITH clause */
-		if (selectStmt->withClause)
-		{
-			qry->hasRecursive = selectStmt->withClause->recursive;
-			qry->cteList = transformWithClause(pstate, selectStmt->withClause);
-		}
-
 		/* Do basic expression transformation (same as a ROW() expr) */
 		exprList = transformExpressionList(pstate,
 										   (List *) linitial(valuesLists));
@@ -1794,6 +1792,13 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
 	qry->commandType = CMD_UPDATE;
 	pstate->p_is_update = true;
 
+	/* process the WITH clause independently of all else */
+	if (stmt->withClause)
+	{
+		qry->hasRecursive = stmt->withClause->recursive;
+		qry->cteList = transformWithClause(pstate, stmt->withClause);
+	}
+
 	qry->resultRelation = setTargetTable(pstate, stmt->relation,
 								  interpretInhOption(stmt->relation->inhOpt),
 										 true,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4054cb1..401c001 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -433,7 +433,7 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
 %type <boolean> xml_whitespace_option
 
 %type <node> 	common_table_expr
-%type <with> 	with_clause
+%type <with> 	with_clause opt_with_clause
 %type <list>	cte_list
 
 %type <list>	window_clause window_definition_list opt_partition_clause
@@ -7268,11 +7268,12 @@ DeallocateStmt: DEALLOCATE name
  *****************************************************************************/
 
 InsertStmt:
-			INSERT INTO qualified_name insert_rest returning_clause
+			opt_with_clause INSERT INTO qualified_name insert_rest returning_clause
 				{
-					$4->relation = $3;
-					$4->returningList = $5;
-					$$ = (Node *) $4;
+					$5->relation = $4;
+					$5->returningList = $6;
+					$5->withClause = $1;
+					$$ = (Node *) $5;
 				}
 		;
 
@@ -7328,14 +7329,15 @@ returning_clause:
  *
  *****************************************************************************/
 
-DeleteStmt: DELETE_P FROM relation_expr_opt_alias
+DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
 			using_clause where_or_current_clause returning_clause
 				{
 					DeleteStmt *n = makeNode(DeleteStmt);
-					n->relation = $3;
-					n->usingClause = $4;
-					n->whereClause = $5;
-					n->returningList = $6;
+					n->relation = $4;
+					n->usingClause = $5;
+					n->whereClause = $6;
+					n->returningList = $7;
+					n->withClause = $1;
 					$$ = (Node *)n;
 				}
 		;
@@ -7390,18 +7392,19 @@ opt_nowait:	NOWAIT							{ $$ = TRUE; }
  *
  *****************************************************************************/
 
-UpdateStmt: UPDATE relation_expr_opt_alias
+UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
 			SET set_clause_list
 			from_clause
 			where_or_current_clause
 			returning_clause
 				{
 					UpdateStmt *n = makeNode(UpdateStmt);
-					n->relation = $2;
-					n->targetList = $4;
-					n->fromClause = $5;
-					n->whereClause = $6;
-					n->returningList = $7;
+					n->relation = $3;
+					n->targetList = $5;
+					n->fromClause = $6;
+					n->whereClause = $7;
+					n->returningList = $8;
+					n->withClause = $1;
 					$$ = (Node *)n;
 				}
 		;
@@ -7743,6 +7746,12 @@ common_table_expr:  name opt_name_list AS select_with_parens
 			}
 		;
 
+opt_with_clause:
+		with_clause								{ $$ = $1; }
+		| /*EMPTY*/								{ $$ = NULL; }
+		;
+
+
 into_clause:
 			INTO OptTempTableName
 				{
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 37ca331..663754a 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1868,6 +1868,14 @@ transformRuleStmt(RuleStmt *stmt, const char *queryString,
 			}
 
 			/*
+			 * OLD/NEW is not allowed in CTE queries.
+			 */
+			if (checkCTEHasOldNew(sub_qry))
+				ereport(ERROR,
+						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+						 errmsg("cannot refer to OLD/NEW in CTE query")));
+
+			/*
 			 * For efficiency's sake, add OLD to the rule action's jointree
 			 * only if it was actually referenced in the statement or qual.
 			 *
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index 5db2522..ca6d494 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -291,6 +291,30 @@ checkExprHasSubLink_walker(Node *node, void *context)
 	return expression_tree_walker(node, checkExprHasSubLink_walker, context);
 }
 
+/*
+ * checkCTEHasOldNew - check if OLD/NEW is referred in CTE queries.
+ */
+bool
+checkCTEHasOldNew(Query *node)
+{
+	ListCell   *l;
+
+	foreach (l, node->cteList)
+	{
+		CommonTableExpr	   *cte = (CommonTableExpr *) lfirst(l);
+		int		new_varno = PRS2_NEW_VARNO;
+		int		old_varno = PRS2_OLD_VARNO;
+
+		/* 1 == the top CTE */
+		if (rangeTableEntry_used(cte->ctequery, new_varno, 1))
+			return true;
+
+		if (rangeTableEntry_used(cte->ctequery, old_varno, 1))
+			return true;
+	}
+
+	return false;
+}
 
 /*
  * OffsetVarNodes - adjust Vars when appending one query's RT to another
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 578b9ce..cec5e19 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -3345,6 +3345,9 @@ get_insert_query_def(Query *query, deparse_context *context)
 	ListCell   *l;
 	List	   *strippedexprs;
 
+	/* Insert the WITH clause if given */
+	get_with_clause(query, context);
+
 	/*
 	 * If it's an INSERT ... SELECT or VALUES (...), (...), ... there will be
 	 * a single RTE for the SELECT or VALUES.
@@ -3482,6 +3485,9 @@ get_update_query_def(Query *query, deparse_context *context)
 	RangeTblEntry *rte;
 	ListCell   *l;
 
+	/* Insert the WITH clause if given */
+	get_with_clause(query, context);
+
 	/*
 	 * Start the query with UPDATE relname SET
 	 */
@@ -3563,6 +3569,9 @@ get_delete_query_def(Query *query, deparse_context *context)
 	StringInfo	buf = context->buf;
 	RangeTblEntry *rte;
 
+	/* Insert the WITH clause if given */
+	get_with_clause(query, context);
+
 	/*
 	 * Start the query with DELETE FROM relname
 	 */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b2f0fef..d93d759 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -896,6 +896,7 @@ typedef struct InsertStmt
 	List	   *cols;			/* optional: names of the target columns */
 	Node	   *selectStmt;		/* the source SELECT/VALUES, or NULL */
 	List	   *returningList;	/* list of expressions to return */
+	WithClause *withClause;		/* WITH clause */
 } InsertStmt;
 
 /* ----------------------
@@ -909,6 +910,7 @@ typedef struct DeleteStmt
 	List	   *usingClause;	/* optional using clause for more tables */
 	Node	   *whereClause;	/* qualifications */
 	List	   *returningList;	/* list of expressions to return */
+	WithClause *withClause;		/* WITH clause */
 } DeleteStmt;
 
 /* ----------------------
@@ -923,6 +925,7 @@ typedef struct UpdateStmt
 	Node	   *whereClause;	/* qualifications */
 	List	   *fromClause;		/* optional from clause for more tables */
 	List	   *returningList;	/* list of expressions to return */
+	WithClause *withClause;		/* WITH clause */
 } UpdateStmt;
 
 /* ----------------------
diff --git a/src/include/rewrite/rewriteManip.h b/src/include/rewrite/rewriteManip.h
index 8daea6e..7a1366b 100644
--- a/src/include/rewrite/rewriteManip.h
+++ b/src/include/rewrite/rewriteManip.h
@@ -56,6 +56,7 @@ extern int	locate_windowfunc(Node *node);
 extern bool checkExprHasAggs(Node *node);
 extern bool checkExprHasWindowFuncs(Node *node);
 extern bool checkExprHasSubLink(Node *node);
+extern bool checkCTEHasOldNew(Query *node);
 
 extern Node *replace_rte_variables(Node *node,
 					  int target_varno, int sublevels_up,
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index e46ed78..a50148b 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -738,6 +738,83 @@ WITH RECURSIVE
 (54 rows)
 
 --
+-- WITH on top of a DML statement
+--
+CREATE TEMPORARY TABLE y (a INTEGER);
+INSERT INTO y SELECT generate_series(1, 10);
+WITH t AS (
+	SELECT a FROM y
+)
+INSERT INTO y
+SELECT a+20 FROM t RETURNING *;
+ a  
+----
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(10 rows)
+
+WITH t AS (
+	SELECT a FROM y
+)
+UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
+ a  
+----
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+(10 rows)
+
+WITH RECURSIVE t(a) AS (
+	SELECT 11
+	UNION ALL
+	SELECT a+1 FROM t WHERE a < 50
+)
+DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
+ a  
+----
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+(10 rows)
+
+SELECT * FROM y;
+ a  
+----
+  1
+  2
+  3
+  4
+  5
+  6
+  7
+  8
+  9
+ 10
+(10 rows)
+
+--
 -- error cases
 --
 -- INTERSECT
@@ -774,8 +851,6 @@ WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
 ERROR:  recursive reference to query "x" must not appear within its non-recursive term
 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
                                               ^
-CREATE TEMPORARY TABLE y (a INTEGER);
-INSERT INTO y SELECT generate_series(1, 10);
 -- LEFT JOIN
 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
 	UNION ALL
@@ -912,6 +987,11 @@ ERROR:  recursive query "foo" column 1 has type numeric(3,0) in non-recursive te
 LINE 2:    (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
                    ^
 HINT:  Cast the output of the non-recursive term to the correct type.
+-- disallow OLD/NEW reference in CTE
+CREATE TEMPORARY TABLE x (n integer);
+CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
+    WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
+ERROR:  cannot refer to OLD/NEW in CTE query
 --
 -- test for bug #4902
 --
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 2cbaa42..7fc70c0 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -339,6 +339,33 @@ WITH RECURSIVE
  SELECT * FROM z;
 
 --
+-- WITH on top of a DML statement
+--
+
+CREATE TEMPORARY TABLE y (a INTEGER);
+INSERT INTO y SELECT generate_series(1, 10);
+
+WITH t AS (
+	SELECT a FROM y
+)
+INSERT INTO y
+SELECT a+20 FROM t RETURNING *;
+
+WITH t AS (
+	SELECT a FROM y
+)
+UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
+
+WITH RECURSIVE t(a) AS (
+	SELECT 11
+	UNION ALL
+	SELECT a+1 FROM t WHERE a < 50
+)
+DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
+
+SELECT * FROM y;
+
+--
 -- error cases
 --
 
@@ -364,9 +391,6 @@ WITH RECURSIVE x(n) AS (SELECT n FROM x)
 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
 	SELECT * FROM x;
 
-CREATE TEMPORARY TABLE y (a INTEGER);
-INSERT INTO y SELECT generate_series(1, 10);
-
 -- LEFT JOIN
 
 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
@@ -470,6 +494,11 @@ WITH RECURSIVE foo(i) AS
    SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
 SELECT * FROM foo;
 
+-- disallow OLD/NEW reference in CTE
+CREATE TEMPORARY TABLE x (n integer);
+CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
+    WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
+
 --
 -- test for bug #4902
 --