From 07f1264dda0e776a7e329b091c127059bce8cc54 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 15 Oct 2010 19:53:59 -0400 Subject: [PATCH] Allow WITH clauses to be attached to INSERT, UPDATE, DELETE statements. This is not the hoped-for facility of using INSERT/UPDATE/DELETE inside a WITH, but rather the other way around. It seems useful in its own right anyway. Note: catversion bumped because, although the contents of stored rules might look compatible, there's actually a subtle semantic change. A single Query containing a WITH and INSERT...VALUES now represents writing the WITH before the INSERT, not before the VALUES. While it's not clear that that matters to anyone, it seems like a good idea to have it cited in the git history for catversion.h. Original patch by Marko Tiikkaja, with updating and cleanup by Hitoshi Harada. --- doc/src/sgml/queries.sgml | 16 +++- doc/src/sgml/ref/delete.sgml | 16 +++- doc/src/sgml/ref/insert.sgml | 25 +++++- doc/src/sgml/ref/update.sgml | 16 +++- src/backend/nodes/copyfuncs.c | 3 + src/backend/nodes/equalfuncs.c | 3 + src/backend/parser/analyze.c | 40 +++++---- src/backend/parser/gram.y | 40 +++++---- src/backend/parser/parse_utilcmd.c | 29 +++++++ src/backend/utils/adt/ruleutils.c | 13 ++- src/include/catalog/catversion.h | 2 +- src/include/nodes/parsenodes.h | 3 + src/test/regress/expected/with.out | 133 +++++++++++++++++++++++++++++ src/test/regress/sql/with.sql | 38 +++++++++ 14 files changed, 333 insertions(+), 44 deletions(-) diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index d16824eddd..f6e081ea73 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -472,7 +472,7 @@ FROM table_reference , table_r (1 row) This is because a restriction placed in the ON - clause is processed before the join, while + clause is processed before the join, while a restriction placed in the WHERE clause is processed after the join. @@ -1139,7 +1139,7 @@ SELECT a "value", b + c AS sum FROM ... The naming of output columns here is different from that done in the FROM clause (see ). It is possible + linkend="queries-table-aliases">). It is possible to rename the same column twice, but the name assigned in the select list is the one that will be passed on. @@ -1539,7 +1539,7 @@ SELECT select_list FROM table_expression WITH provides a way to write subqueries for use in a larger - SELECT query. The subqueries can be thought of as defining + query. The subqueries can be thought of as defining temporary tables that exist just for this query. One use of this feature is to break down complicated queries into simpler parts. An example is: @@ -1791,12 +1791,20 @@ SELECT n FROM t LIMIT 100; However, the other side of this coin is that the optimizer is less able to push restrictions from the parent query down into a WITH query than an ordinary sub-query. The WITH query will generally be - evaluated as stated, without suppression of rows that the parent query + evaluated as written, without suppression of rows that the parent query might discard afterwards. (But, as mentioned above, evaluation might stop early if the reference(s) to the query demand only a limited number of rows.) + + The examples above only show WITH being used with + SELECT, but it can be attached in the same way to + INSERT, UPDATE, or DELETE. + In each case it effectively provides temporary table(s) that can + be referred to in the main command. + + diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index c87f35c9b4..9406f4e3af 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -21,6 +21,7 @@ PostgreSQL documentation +[ WITH [ RECURSIVE ] with_query [, ...] ] DELETE FROM [ ONLY ] table [ [ AS ] alias ] [ USING using_list ] [ WHERE condition | WHERE CURRENT OF cursor_name ] @@ -83,6 +84,18 @@ DELETE FROM [ ONLY ] table [ [ AS ] Parameters + + with_query + + + The WITH clause allows you to specify one or more + subqueries that can be referenced by name in the DELETE + query. See and + for details. + + + + ONLY @@ -272,7 +285,8 @@ DELETE FROM tasks WHERE CURRENT OF c_tasks; This command conforms to the SQL standard, except that the USING and RETURNING clauses - are PostgreSQL extensions. + are PostgreSQL extensions, as is the ability + to use WITH with DELETE. diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 6d17ef05f7..629cc7ea55 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -21,6 +21,7 @@ PostgreSQL documentation +[ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table [ ( column [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] @@ -84,6 +85,26 @@ INSERT INTO table [ ( Parameters + + with_query + + + The WITH clause allows you to specify one or more + subqueries that can be referenced by name in the INSERT + query. See and + for details. + + + It is possible for the query + (SELECT statement) + to also contain a WITH clause. In such a case both + sets of with_query can be referenced within + the query, but the + second one takes precedence since it is more closely nested. + + + + table @@ -287,7 +308,9 @@ INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') INSERT conforms to the SQL standard, except that the RETURNING clause is a - PostgreSQL extension. Also, the case in + PostgreSQL extension, as is the ability + to use WITH with INSERT. + Also, the case in which a column name list is omitted, but not all the columns are filled from the VALUES clause or query, is disallowed by the standard. diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index c89763492f..5968db1f6f 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -21,6 +21,7 @@ PostgreSQL documentation +[ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table [ [ AS ] alias ] SET { column = { expression | DEFAULT } | ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] @@ -79,6 +80,18 @@ UPDATE [ ONLY ] table [ [ AS ] Parameters + + with_query + + + The WITH clause allows you to specify one or more + subqueries that can be referenced by name in the UPDATE + query. See and + for details. + + + + table @@ -345,7 +358,8 @@ UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films; This command conforms to the SQL standard, except that the FROM and RETURNING clauses - are PostgreSQL extensions. + are PostgreSQL extensions, as is the ability + to use WITH with UPDATE. diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 6ce3984bff..508d7c70b1 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2313,6 +2313,7 @@ _copyInsertStmt(InsertStmt *from) COPY_NODE_FIELD(cols); COPY_NODE_FIELD(selectStmt); COPY_NODE_FIELD(returningList); + COPY_NODE_FIELD(withClause); return newnode; } @@ -2326,6 +2327,7 @@ _copyDeleteStmt(DeleteStmt *from) COPY_NODE_FIELD(usingClause); COPY_NODE_FIELD(whereClause); COPY_NODE_FIELD(returningList); + COPY_NODE_FIELD(withClause); return newnode; } @@ -2340,6 +2342,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 6bad724db6..19262aad66 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 21342e8a9d..bb2bf04e17 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), @@ -340,9 +347,19 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) ListCell *attnos; ListCell *lc; + /* There can't be any outer WITH to worry about */ + Assert(pstate->p_ctenamespace == NIL); + 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 +393,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 +533,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 +626,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 +1795,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 3a74fa5082..609c472701 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 xml_whitespace_option %type common_table_expr -%type with_clause +%type with_clause opt_with_clause %type cte_list %type window_clause window_definition_list opt_partition_clause @@ -7269,11 +7269,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; } ; @@ -7329,14 +7330,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; } ; @@ -7391,18 +7393,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; } ; @@ -7744,6 +7747,11 @@ 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 37ca331c21..a8aee204c7 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1867,6 +1867,35 @@ transformRuleStmt(RuleStmt *stmt, const char *queryString, break; } + /* + * OLD/NEW are not allowed in WITH queries, because they would + * amount to outer references for the WITH, which we disallow. + * However, they were already in the outer rangetable when we + * analyzed the query, so we have to check. + * + * Note that in the INSERT...SELECT case, we need to examine + * the CTE lists of both top_subqry and sub_qry. + * + * Note that we aren't digging into the body of the query + * looking for WITHs in nested sub-SELECTs. A WITH down there + * can legitimately refer to OLD/NEW, because it'd be an + * indirect-correlated outer reference. + */ + if (rangeTableEntry_used((Node *) top_subqry->cteList, + PRS2_OLD_VARNO, 0) || + rangeTableEntry_used((Node *) sub_qry->cteList, + PRS2_OLD_VARNO, 0)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot refer to OLD within WITH query"))); + if (rangeTableEntry_used((Node *) top_subqry->cteList, + PRS2_NEW_VARNO, 0) || + rangeTableEntry_used((Node *) sub_qry->cteList, + PRS2_NEW_VARNO, 0)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot refer to NEW within WITH 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/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index b5437612a9..22ba948e73 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -3352,6 +3352,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. @@ -3451,15 +3454,11 @@ get_insert_query_def(Query *query, deparse_context *context) } else if (values_rte) { - /* A WITH clause is possible here */ - get_with_clause(query, context); /* Add the multi-VALUES expression lists */ get_values_def(values_rte->values_lists, context); } else { - /* A WITH clause is possible here */ - get_with_clause(query, context); /* Add the single-VALUES expression list */ appendContextKeyword(context, "VALUES (", -PRETTYINDENT_STD, PRETTYINDENT_STD, 2); @@ -3489,6 +3488,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 */ @@ -3570,6 +3572,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/catalog/catversion.h b/src/include/catalog/catversion.h index 910474cdcf..28fbffa362 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201010101 +#define CATALOG_VERSION_NO 201010151 #endif diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index ca225d06ec..e0bdebd0ab 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/test/regress/expected/with.out b/src/test/regress/expected/with.out index e46ed78ae6..93b67e3b74 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -737,6 +737,134 @@ WITH RECURSIVE 10 (54 rows) +-- +-- Test WITH attached to 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) + +SELECT * FROM y; + a +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 21 + 22 + 23 + 24 + 25 + 26 + 27 + 28 + 29 + 30 +(20 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) + +SELECT * FROM y; + a +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + 13 + 14 + 15 + 16 + 17 + 18 + 19 + 20 +(20 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) + +DROP TABLE y; -- -- error cases -- @@ -912,6 +1040,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 within WITH query -- -- test for bug #4902 -- diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index 2cbaa42492..1878eb65b2 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -338,6 +338,39 @@ WITH RECURSIVE (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10) SELECT * FROM z; +-- +-- Test WITH attached to 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 *; + +SELECT * FROM y; + +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; + +SELECT * FROM y; + +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; + +DROP TABLE y; + -- -- error cases -- @@ -470,6 +503,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 -- -- 2.40.0