On Thu, 11 Feb 2010 19:28:28 +0200, I wrote:
> On Thu, 11 Feb 2010 10:53:22 -0500, Robert Haas <robertmh...@gmail.com>
> wrote:
>> On Thu, Feb 11, 2010 at 8:46 AM, Marko Tiikkaja
>> <marko.tiikk...@cs.helsinki.fi> wrote:
>>> On 2010-02-11 03:44 +0200, I wrote:
>>>> I'm going to have to disappoint a bunch of people and give up. :-(
>>>
>>> Btw. would it make sense to apply the WITH-on-top-of-DML part of this
>>> patch? At least to me, this seems useful because you can write a
>>> RECURSIVE SELECT and then use UPDATE .. FROM or DELETE .. USING on that
>>> CTE.
>>
>> Hmm, that's a thought. Can you split out just that part?
>
> Here's the patch. It's the same as the stuff in writeable CTE patches,
but
> I added regression tests.
Whoops. The reference section in docs still had some traces of writeable
CTEs. Updated patch attached.
Regards,
Marko Tiikkaja
*** 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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
