(Oops, this didn't go to -HACKERS)
On 2010-10-04 2:46 PM +0300, Erik Rijkers wrote:
(HEAD from git://git.postgresql.org/git/postgresql.git)
The patch applies only with error.
If that error is ignored, the regression 'with' test failes.
If that is also ignored, it runs.
This patch conflicted with Tom's WITH .. INSERT change. I tweaked the
patch just a bit and it now passes all regression tests so I can review
it. New version attached for documentation purposes.
Regards,
Marko Tiikkaja
*** a/doc/src/sgml/ref/delete.sgml
--- b/doc/src/sgml/ref/delete.sgml
***************
*** 21,26 **** PostgreSQL documentation
--- 21,27 ----
<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,89 **** DELETE FROM [ ONLY ] <replaceable
class="PARAMETER">table</replaceable> [ [ AS ]
--- 85,102 ----
<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>
*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***************
*** 21,26 **** PostgreSQL documentation
--- 21,27 ----
<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,90 **** INSERT INTO <replaceable class="PARAMETER">table</replaceable> [
( <replaceable
--- 86,109 ----
<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>
***************
*** 129,135 **** INSERT INTO <replaceable class="PARAMETER">table</replaceable>
[ ( <replaceable
<listitem>
<para>
The corresponding <replaceable>column</replaceable> will be filled with
! its default value.
</para>
</listitem>
</varlistentry>
--- 148,155 ----
<listitem>
<para>
The corresponding <replaceable>column</replaceable> will be filled with
! its default value. This clause is allowed in a simple VALUES list
! without additional (LIMIT, etc.) clauses.
</para>
</listitem>
</varlistentry>
*** a/doc/src/sgml/ref/update.sgml
--- b/doc/src/sgml/ref/update.sgml
***************
*** 21,26 **** PostgreSQL documentation
--- 21,27 ----
<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,85 **** UPDATE [ ONLY ] <replaceable
class="PARAMETER">table</replaceable> [ [ AS ] <rep
--- 81,98 ----
<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>
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 2288,2293 **** _copyInsertStmt(InsertStmt *from)
--- 2288,2294 ----
COPY_NODE_FIELD(cols);
COPY_NODE_FIELD(selectStmt);
COPY_NODE_FIELD(returningList);
+ COPY_NODE_FIELD(withClause);
return newnode;
}
***************
*** 2301,2306 **** _copyDeleteStmt(DeleteStmt *from)
--- 2302,2308 ----
COPY_NODE_FIELD(usingClause);
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(returningList);
+ COPY_NODE_FIELD(withClause);
return newnode;
}
***************
*** 2315,2320 **** _copyUpdateStmt(UpdateStmt *from)
--- 2317,2323 ----
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
***************
*** 890,895 **** _equalInsertStmt(InsertStmt *a, InsertStmt *b)
--- 890,896 ----
COMPARE_NODE_FIELD(cols);
COMPARE_NODE_FIELD(selectStmt);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_NODE_FIELD(withClause);
return true;
}
***************
*** 901,906 **** _equalDeleteStmt(DeleteStmt *a, DeleteStmt *b)
--- 902,908 ----
COMPARE_NODE_FIELD(usingClause);
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_NODE_FIELD(withClause);
return true;
}
***************
*** 913,918 **** _equalUpdateStmt(UpdateStmt *a, UpdateStmt *b)
--- 915,921 ----
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
***************
*** 283,288 **** transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
--- 283,295 ----
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,348 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
--- 350,362 ----
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,383 **** 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
{
--- 390,395 ----
***************
*** 518,530 **** 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);
--- 530,535 ----
***************
*** 618,630 **** 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));
--- 623,628 ----
***************
*** 1045,1051 **** transformValuesClause(ParseState *pstate, SelectStmt *stmt)
if (IsA(col, SetToDefault))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("DEFAULT can only
appear in a VALUES list within INSERT"),
parser_errposition(pstate,
exprLocation(col))));
colexprs[i] = lappend(colexprs[i], col);
i++;
--- 1043,1050 ----
if (IsA(col, SetToDefault))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("DEFAULT can only
appear in a simple VALUES list within INSERT"),
! errhint("A simple VALUES list
means it isn't modified by any of ORDER BY, OFFSET, LIMIT or WITH"),
parser_errposition(pstate,
exprLocation(col))));
colexprs[i] = lappend(colexprs[i], col);
i++;
***************
*** 1794,1799 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
--- 1793,1805 ----
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,
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 433,439 **** static RangeVar *makeRangeVarFromAnyName(List *names, int
position, core_yyscan_
%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
--- 433,439 ----
%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
***************
*** 7268,7278 **** DeallocateStmt: DEALLOCATE name
*****************************************************************************/
InsertStmt:
! INSERT INTO qualified_name insert_rest returning_clause
{
! $4->relation = $3;
! $4->returningList = $5;
! $$ = (Node *) $4;
}
;
--- 7268,7279 ----
*****************************************************************************/
InsertStmt:
! opt_with_clause INSERT INTO qualified_name insert_rest
returning_clause
{
! $5->relation = $4;
! $5->returningList = $6;
! $5->withClause = $1;
! $$ = (Node *) $5;
}
;
***************
*** 7328,7341 **** 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;
}
;
--- 7329,7343 ----
*
*****************************************************************************/
! 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;
}
;
***************
*** 7390,7407 **** 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;
}
;
--- 7392,7410 ----
*
*****************************************************************************/
! 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;
}
;
***************
*** 7743,7748 **** common_table_expr: name opt_name_list AS select_with_parens
--- 7746,7757 ----
}
;
+ opt_with_clause:
+ with_clause
{ $$ = $1; }
+ | /*EMPTY*/
{ $$ = NULL; }
+ ;
+
+
into_clause:
INTO OptTempTableName
{
*** a/src/backend/parser/parse_utilcmd.c
--- b/src/backend/parser/parse_utilcmd.c
***************
*** 1868,1873 **** transformRuleStmt(RuleStmt *stmt, const char *queryString,
--- 1868,1881 ----
}
/*
+ * 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.
*
*** a/src/backend/rewrite/rewriteManip.c
--- b/src/backend/rewrite/rewriteManip.c
***************
*** 291,296 **** checkExprHasSubLink_walker(Node *node, void *context)
--- 291,320 ----
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
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 3345,3350 **** get_insert_query_def(Query *query, deparse_context *context)
--- 3345,3353 ----
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,3487 **** get_update_query_def(Query *query, deparse_context *context)
--- 3485,3493 ----
RangeTblEntry *rte;
ListCell *l;
+ /* Insert the WITH clause if given */
+ get_with_clause(query, context);
+
/*
* Start the query with UPDATE relname SET
*/
***************
*** 3563,3568 **** get_delete_query_def(Query *query, deparse_context *context)
--- 3569,3577 ----
StringInfo buf = context->buf;
RangeTblEntry *rte;
+ /* Insert the WITH clause if given */
+ get_with_clause(query, context);
+
/*
* Start the query with DELETE FROM relname
*/
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 896,901 **** typedef struct InsertStmt
--- 896,902 ----
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,914 **** typedef struct DeleteStmt
--- 910,916 ----
List *usingClause; /* optional using clause for more
tables */
Node *whereClause; /* qualifications */
List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} DeleteStmt;
/* ----------------------
***************
*** 923,928 **** typedef struct UpdateStmt
--- 925,931 ----
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/include/rewrite/rewriteManip.h
--- b/src/include/rewrite/rewriteManip.h
***************
*** 56,61 **** extern int locate_windowfunc(Node *node);
--- 56,62 ----
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,
*** a/src/test/regress/expected/with.out
--- b/src/test/regress/expected/with.out
***************
*** 738,743 **** WITH RECURSIVE
--- 738,820 ----
(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,781 **** 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
--- 851,856 ----
***************
*** 912,917 **** ERROR: recursive query "foo" column 1 has type numeric(3,0)
in non-recursive te
--- 987,997 ----
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
--
*** a/src/test/regress/sql/with.sql
--- b/src/test/regress/sql/with.sql
***************
*** 339,344 **** WITH RECURSIVE
--- 339,371 ----
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,372 **** 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
--- 391,396 ----
***************
*** 470,475 **** WITH RECURSIVE foo(i) AS
--- 494,504 ----
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
--
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
