with_on_dml2.patch

*** a/doc/src/sgml/ref/delete.sgml
--- b/doc/src/sgml/ref/delete.sgml
***************
*** 21,30 **** PostgreSQL documentation
--- 21,36 ----
  
   <refsynopsisdiv>
  <synopsis>
+ [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
  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> ]
      [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
+ 
+ <phrase>where <replaceable class="parameter">with_query</replaceable> is:</phrase>
+ 
+     <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS ( <replaceable class="parameter">select</replaceable> )
+ 
  </synopsis>
   </refsynopsisdiv>
  
***************
*** 84,89 **** DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ]
--- 90,104 ----
  
    <variablelist>
     <varlistentry>
+     <term><replaceable class="PARAMETER">with_query</replaceable></term>
+     <listitem>
+      <para>
+       For information about with_query, see
+       <xref linkend="sql-with" endterm="sql-with-title">.
+      </para>
+     </listitem>
+    </varlistentry>
+    <varlistentry>
      <term><literal>ONLY</></term>
      <listitem>
       <para>
*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***************
*** 21,29 **** PostgreSQL documentation
--- 21,36 ----
  
   <refsynopsisdiv>
  <synopsis>
+ [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
  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> ] [, ...] ]
+ 
+ <phrase>where <replaceable class="parameter">with_query</replaceable> is:</phrase>
+ 
+     <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS ( <replaceable class="parameter">select</replaceable> )
+ 
+ 
  </synopsis>
   </refsynopsisdiv>
  
***************
*** 85,90 **** INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
--- 92,106 ----
  
    <variablelist>
     <varlistentry>
+     <term><replaceable class="PARAMETER">with_query</replaceable></term>
+     <listitem>
+      <para>
+       For information about with_query, see
+       <xref linkend="sql-with" endterm="sql-with-title">.
+      </para>
+     </listitem>
+    </varlistentry>
+    <varlistentry>
      <term><replaceable class="PARAMETER">table</replaceable></term>
      <listitem>
       <para>
*** a/doc/src/sgml/ref/update.sgml
--- b/doc/src/sgml/ref/update.sgml
***************
*** 21,32 **** PostgreSQL documentation
--- 21,38 ----
  
   <refsynopsisdiv>
  <synopsis>
+ [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
  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 } [, ...] ) } [, ...]
      [ FROM <replaceable class="PARAMETER">from_list</replaceable> ]
      [ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
      [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
+ 
+ <phrase>where <replaceable class="parameter">with_query</replaceable> is:</phrase>
+ 
+     <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS ( <replaceable class="parameter">select</replaceable> )
+ 
  </synopsis>
   </refsynopsisdiv>
  
***************
*** 80,85 **** UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
--- 86,100 ----
  
    <variablelist>
     <varlistentry>
+     <term><replaceable class="PARAMETER">with_query</replaceable></term>
+     <listitem>
+      <para>
+       For information about with_query, see
+       <xref linkend="sql-with" endterm="sql-with-title">.
+      </para>
+     </listitem>
+    </varlistentry>
+    <varlistentry>
      <term><replaceable class="PARAMETER">table</replaceable></term>
      <listitem>
       <para>
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 2260,2265 **** _copyInsertStmt(InsertStmt *from)
--- 2260,2266 ----
  	COPY_NODE_FIELD(cols);
  	COPY_NODE_FIELD(selectStmt);
  	COPY_NODE_FIELD(returningList);
+ 	COPY_NODE_FIELD(withClause);
  
  	return newnode;
  }
***************
*** 2273,2278 **** _copyDeleteStmt(DeleteStmt *from)
--- 2274,2280 ----
  	COPY_NODE_FIELD(usingClause);
  	COPY_NODE_FIELD(whereClause);
  	COPY_NODE_FIELD(returningList);
+ 	COPY_NODE_FIELD(withClause);
  
  	return newnode;
  }
***************
*** 2287,2292 **** _copyUpdateStmt(UpdateStmt *from)
--- 2289,2295 ----
  	COPY_NODE_FIELD(whereClause);
  	COPY_NODE_FIELD(fromClause);
  	COPY_NODE_FIELD(returningList);
+ 	COPY_NODE_FIELD(withClause);
  
  	return newnode;
  }
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 888,893 **** _equalInsertStmt(InsertStmt *a, InsertStmt *b)
--- 888,894 ----
  	COMPARE_NODE_FIELD(cols);
  	COMPARE_NODE_FIELD(selectStmt);
  	COMPARE_NODE_FIELD(returningList);
+ 	COMPARE_NODE_FIELD(withClause);
  
  	return true;
  }
***************
*** 899,904 **** _equalDeleteStmt(DeleteStmt *a, DeleteStmt *b)
--- 900,906 ----
  	COMPARE_NODE_FIELD(usingClause);
  	COMPARE_NODE_FIELD(whereClause);
  	COMPARE_NODE_FIELD(returningList);
+ 	COMPARE_NODE_FIELD(withClause);
  
  	return true;
  }
***************
*** 911,916 **** _equalUpdateStmt(UpdateStmt *a, UpdateStmt *b)
--- 913,919 ----
  	COMPARE_NODE_FIELD(whereClause);
  	COMPARE_NODE_FIELD(fromClause);
  	COMPARE_NODE_FIELD(returningList);
+ 	COMPARE_NODE_FIELD(withClause);
  
  	return true;
  }
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 290,295 **** transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
--- 290,302 ----
  
  	qry->distinctClause = NIL;
  
+ 	/* process the WITH clause */
+ 	if (stmt->withClause)
+ 	{
+ 		qry->hasRecursive = stmt->withClause->recursive;
+ 		qry->cteList = transformWithClause(pstate, stmt->withClause);
+ 	}
+ 
  	/*
  	 * The USING clause is non-standard SQL syntax, and is equivalent in
  	 * functionality to the FROM list that can be specified for UPDATE. The
***************
*** 342,347 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
--- 349,361 ----
  	qry->commandType = CMD_INSERT;
  	pstate->p_is_insert = true;
  
+ 	/* process the WITH clause */
+ 	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
***************
*** 366,373 **** 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
  	{
--- 380,385 ----
***************
*** 1735,1740 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
--- 1747,1759 ----
  										 true,
  										 ACL_UPDATE);
  
+ 	/* process the WITH clause */
+ 	if (stmt->withClause)
+ 	{
+ 		qry->hasRecursive = stmt->withClause->recursive;
+ 		qry->cteList = transformWithClause(pstate, stmt->withClause);
+ 	}
+ 
  	/*
  	 * the FROM clause is non-standard SQL syntax. We used to be able to do
  	 * this with REPLACE in POSTQUEL so we keep the feature.
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 429,435 **** static TypeName *TableFuncTypeName(List *columns);
  %type <boolean> xml_whitespace_option
  
  %type <node> 	common_table_expr
! %type <with> 	with_clause
  %type <list>	cte_list
  
  %type <list>	window_clause window_definition_list opt_partition_clause
--- 429,435 ----
  %type <boolean> xml_whitespace_option
  
  %type <node> 	common_table_expr
! %type <with> 	with_clause opt_with_clause
  %type <list>	cte_list
  
  %type <list>	window_clause window_definition_list opt_partition_clause
***************
*** 7071,7081 **** DeallocateStmt: DEALLOCATE name
   *****************************************************************************/
  
  InsertStmt:
! 			INSERT INTO qualified_name insert_rest returning_clause
  				{
! 					$4->relation = $3;
! 					$4->returningList = $5;
! 					$$ = (Node *) $4;
  				}
  		;
  
--- 7071,7082 ----
   *****************************************************************************/
  
  InsertStmt:
! 			opt_with_clause INSERT INTO qualified_name insert_rest returning_clause
  				{
! 					$5->relation = $4;
! 					$5->returningList = $6;
! 					$5->withClause = $1;
! 					$$ = (Node *) $5;
  				}
  		;
  
***************
*** 7131,7144 **** returning_clause:
   *
   *****************************************************************************/
  
! DeleteStmt: 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;
  					$$ = (Node *)n;
  				}
  		;
--- 7132,7146 ----
   *
   *****************************************************************************/
  
! 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 = $4;
! 					n->usingClause = $5;
! 					n->whereClause = $6;
! 					n->returningList = $7;
! 					n->withClause = $1;
  					$$ = (Node *)n;
  				}
  		;
***************
*** 7193,7210 **** opt_nowait:	NOWAIT							{ $$ = TRUE; }
   *
   *****************************************************************************/
  
! UpdateStmt: 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;
  					$$ = (Node *)n;
  				}
  		;
--- 7195,7213 ----
   *
   *****************************************************************************/
  
! 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 = $3;
! 					n->targetList = $5;
! 					n->fromClause = $6;
! 					n->whereClause = $7;
! 					n->returningList = $8;
! 					n->withClause = $1;
  					$$ = (Node *)n;
  				}
  		;
***************
*** 7530,7535 **** with_clause:
--- 7533,7542 ----
  			}
  		;
  
+ opt_with_clause:
+ 		with_clause								{ $$ = $1; }
+ 		| /*EMPTY*/								{ $$ = NULL; }
+ 
  cte_list:
  		common_table_expr						{ $$ = list_make1($1); }
  		| cte_list ',' common_table_expr		{ $$ = lappend($1, $3); }
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 880,885 **** typedef struct InsertStmt
--- 880,886 ----
  	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;
  
  /* ----------------------
***************
*** 893,898 **** typedef struct DeleteStmt
--- 894,900 ----
  	List	   *usingClause;	/* optional using clause for more tables */
  	Node	   *whereClause;	/* qualifications */
  	List	   *returningList;	/* list of expressions to return */
+ 	WithClause *withClause;		/* WITH clause */
  } DeleteStmt;
  
  /* ----------------------
***************
*** 907,912 **** typedef struct UpdateStmt
--- 909,915 ----
  	Node	   *whereClause;	/* qualifications */
  	List	   *fromClause;		/* optional from clause for more tables */
  	List	   *returningList;	/* list of expressions to return */
+ 	WithClause *withClause;		/* WITH clause */
  } UpdateStmt;
  
  /* ----------------------
*** a/src/test/regress/expected/with.out
--- b/src/test/regress/expected/with.out
***************
*** 1026,1028 **** SELECT * FROM t;
--- 1026,1087 ----
   10
  (55 rows)
  
+ --
+ -- WITH on top of a DML statement
+ --
+ 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)
+ 
*** a/src/test/regress/sql/with.sql
--- b/src/test/regress/sql/with.sql
***************
*** 500,502 **** WITH RECURSIVE t(j) AS (
--- 500,523 ----
      SELECT j+1 FROM t WHERE j < 10
  )
  SELECT * FROM t;
+ 
+ --
+ -- WITH on top of a DML statement
+ --
+ 
+ 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;