David Fetter wrote:
> I was discussing this with Andrew Gierth in IRC, who thought that
> putting RETURNING inside the WITH clause would be relatively easy, at
> least for the parser and planner. For the executor, he suggested that
> one approach might be to make INSERT, UPDATE and DELETE into their own
> nodes.
David asked me to post his (and mine) experimental work in progress
patch for this here. The patch in the current state does not work. It
dies in executor on:
ERROR: attribute 1 has wrong type
DETAIL: Table has type tid, but query expects integer.
Since I know nothing about postgres' executor I am only guessing it
thinks the query is SELECT instead of DELETE RETURNING.
Also I think those query->commandType == CMD_SELECT ? query->targetList
: query->returningList in several places might not be the right way to go.
Anyway it's beginning and maybe somebody who knows what he is doing
could help or continue the work.
--
Regards
Petr Jelinek (PJMODOS)
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 015dfdc..b2d17ab 100644
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
*************** bool
*** 2354,2359 ****
--- 2354,2403 ----
return true;
}
break;
+ case T_InsertStmt:
+ {
+ InsertStmt *stmt = (InsertStmt *) node;
+
+ if (walker(stmt->relation, context))
+ return true;
+ if (walker(stmt->cols, context))
+ return true;
+ if (walker(stmt->selectStmt, context))
+ return true;
+ if (walker(stmt->returningList, context))
+ return true;
+ }
+ break;
+ case T_DeleteStmt:
+ {
+ DeleteStmt *stmt = (DeleteStmt *) node;
+
+ if (walker(stmt->relation, context))
+ return true;
+ if (walker(stmt->usingClause, context))
+ return true;
+ if (walker(stmt->whereClause, context))
+ return true;
+ if (walker(stmt->returningList, context))
+ return true;
+ }
+ break;
+ case T_UpdateStmt:
+ {
+ UpdateStmt *stmt = (UpdateStmt *) node;
+
+ if (walker(stmt->relation, context))
+ return true;
+ if (walker(stmt->targetList, context))
+ return true;
+ if (walker(stmt->whereClause, context))
+ return true;
+ if (walker(stmt->fromClause, context))
+ return true;
+ if (walker(stmt->returningList, context))
+ return true;
+ }
+ break;
case T_A_Expr:
{
A_Expr *expr = (A_Expr *) node;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9a45355..9e66536 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** cte_list:
*** 7028,7034 ****
| cte_list ',' common_table_expr { $$ = lappend($1, $3); }
;
! common_table_expr: name opt_name_list AS select_with_parens
{
CommonTableExpr *n = makeNode(CommonTableExpr);
n->ctename = $1;
--- 7028,7035 ----
| cte_list ',' common_table_expr { $$ = lappend($1, $3); }
;
! common_table_expr:
! name opt_name_list AS select_with_parens
{
CommonTableExpr *n = makeNode(CommonTableExpr);
n->ctename = $1;
*************** common_table_expr: name opt_name_list A
*** 7037,7042 ****
--- 7038,7070 ----
n->location = @1;
$$ = (Node *) n;
}
+ | name opt_name_list AS '(' InsertStmt ')'
+ {
+ CommonTableExpr *n = makeNode(CommonTableExpr);
+ n->ctename = $1;
+ n->aliascolnames = $2;
+ n->ctequery = $5;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | name opt_name_list AS '(' UpdateStmt ')'
+ {
+ CommonTableExpr *n = makeNode(CommonTableExpr);
+ n->ctename = $1;
+ n->aliascolnames = $2;
+ n->ctequery = $5;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | name opt_name_list AS '(' DeleteStmt ')'
+ {
+ CommonTableExpr *n = makeNode(CommonTableExpr);
+ n->ctename = $1;
+ n->aliascolnames = $2;
+ n->ctequery = $5;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
;
into_clause:
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
index 988e8eb..ef26ea6 100644
*** a/src/backend/parser/parse_cte.c
--- b/src/backend/parser/parse_cte.c
*************** analyzeCTE(ParseState *pstate, CommonTab
*** 249,255 ****
Query *query;
/* Analysis not done already */
! Assert(IsA(cte->ctequery, SelectStmt));
query = parse_sub_analyze(cte->ctequery, pstate);
cte->ctequery = (Node *) query;
--- 249,256 ----
Query *query;
/* Analysis not done already */
! /* This needs to be one of SelectStmt, InsertStmt, UpdateStmt, DeleteStmt instead of:
! * Assert(IsA(cte->ctequery, SelectStmt)); */
query = parse_sub_analyze(cte->ctequery, pstate);
cte->ctequery = (Node *) query;
*************** analyzeCTE(ParseState *pstate, CommonTab
*** 257,268 ****
/*
* Check that we got something reasonable. Many of these conditions are
* impossible given restrictions of the grammar, but check 'em anyway.
! * (These are the same checks as in transformRangeSubselect.)
*/
! if (!IsA(query, Query) ||
! query->commandType != CMD_SELECT ||
! query->utilityStmt != NULL)
! elog(ERROR, "unexpected non-SELECT command in subquery in WITH");
if (query->intoClause)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
--- 258,274 ----
/*
* Check that we got something reasonable. Many of these conditions are
* impossible given restrictions of the grammar, but check 'em anyway.
! * (In addition to the same checks as in transformRangeSubselect,
! * this adds checks for (INSERT|UPDATE|DELETE)...RETURNING.)
*/
! if ((!IsA(query, Query) ||
! query->commandType != CMD_SELECT ||
! query->utilityStmt != NULL) &&
! !((query->commandType == CMD_INSERT ||
! query->commandType == CMD_UPDATE ||
! query->commandType == CMD_DELETE) &&
! query->returningList != NULL))
! elog(ERROR, "unexpected non-row-returning command in subquery in WITH");
if (query->intoClause)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
*************** analyzeCTE(ParseState *pstate, CommonTab
*** 273,279 ****
if (!cte->cterecursive)
{
/* Compute the output column names/types if not done yet */
! analyzeCTETargetList(pstate, cte, query->targetList);
}
else
{
--- 279,285 ----
if (!cte->cterecursive)
{
/* Compute the output column names/types if not done yet */
! analyzeCTETargetList(pstate, cte, query->commandType == CMD_SELECT ? query->targetList :
query->returningList);
}
else
{
*************** analyzeCTE(ParseState *pstate, CommonTab
*** 291,297 ****
lctyp = list_head(cte->ctecoltypes);
lctypmod = list_head(cte->ctecoltypmods);
varattno = 0;
! foreach(lctlist, query->targetList)
{
TargetEntry *te = (TargetEntry *) lfirst(lctlist);
Node *texpr;
--- 297,303 ----
lctyp = list_head(cte->ctecoltypes);
lctypmod = list_head(cte->ctecoltypmods);
varattno = 0;
! foreach(lctlist, query->commandType == CMD_SELECT ? query->targetList : query->returningList)
{
TargetEntry *te = (TargetEntry *) lfirst(lctlist);
Node *texpr;
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 08b8edb..cc767d8 100644
*** a/src/backend/parser/parse_target.c
--- b/src/backend/parser/parse_target.c
*************** markTargetListOrigin(ParseState *pstate,
*** 310,319 ****
{
CommonTableExpr *cte = GetCTEForRTE(pstate, rte, netlevelsup);
TargetEntry *ste;
/* should be analyzed by now */
Assert(IsA(cte->ctequery, Query));
! ste = get_tle_by_resno(((Query *) cte->ctequery)->targetList,
attnum);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
--- 310,321 ----
{
CommonTableExpr *cte = GetCTEForRTE(pstate, rte, netlevelsup);
TargetEntry *ste;
+ Query *query;
/* should be analyzed by now */
Assert(IsA(cte->ctequery, Query));
! query = (Query *) cte->ctequery;
! ste = get_tle_by_resno(query->commandType == CMD_SELECT ? query->targetList : query->returningList,
attnum);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
*************** expandRecordVariable(ParseState *pstate,
*** 1233,1242 ****
{
CommonTableExpr *cte = GetCTEForRTE(pstate, rte, netlevelsup);
TargetEntry *ste;
/* should be analyzed by now */
Assert(IsA(cte->ctequery, Query));
! ste = get_tle_by_resno(((Query *) cte->ctequery)->targetList,
attnum);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
--- 1235,1246 ----
{
CommonTableExpr *cte = GetCTEForRTE(pstate, rte, netlevelsup);
TargetEntry *ste;
+ Query *query;
/* should be analyzed by now */
Assert(IsA(cte->ctequery, Query));
! query = (Query *) cte->ctequery;
! ste = get_tle_by_resno(query->commandType == CMD_SELECT ? query->targetList : query->returningList,
attnum);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d302fb8..d6d9a6d 100644
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
*************** get_name_for_var_field(Var *var, int fie
*** 3801,3807 ****
if (lc != NULL)
{
Query *ctequery = (Query *) cte->ctequery;
! TargetEntry *ste = get_tle_by_resno(ctequery->targetList,
attnum);
if (ste == NULL || ste->resjunk)
--- 3801,3807 ----
if (lc != NULL)
{
Query *ctequery = (Query *) cte->ctequery;
! TargetEntry *ste = get_tle_by_resno(ctequery->commandType == CMD_SELECT ? ctequery->targetList :
ctequery->returningList,
attnum);
if (ste == NULL || ste->resjunk)
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 4a2f18c..cb603ca 100644
*** a/src/test/regress/expected/with.out
--- b/src/test/regress/expected/with.out
*************** ERROR: recursive query "foo" column 1 h
*** 912,914 ****
--- 912,934 ----
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.
+
+ -- DELETE inside the CTE
+ CREATE TEMPORARY TABLE t(i INTEGER);
+ INSERT INTO t(i) SELECT * FROM generate_series(1,10);
+
+ WITH RECURSIVE foo(i) AS (
+ DELETE FROM t RETURNING i
+ )
+ SELECT i FROM foo ORDER BY i;
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ (10 rows)
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index c736441..eb83aab 100644
*** a/src/test/regress/sql/with.sql
--- b/src/test/regress/sql/with.sql
*************** WITH RECURSIVE foo(i) AS
*** 469,471 ****
--- 469,480 ----
UNION ALL
SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
SELECT * FROM foo;
+
+ -- DELETE inside the CTE
+ CREATE TEMPORARY TABLE t(i INTEGER);
+ INSERT INTO t(i) SELECT * FROM generate_series(1,10);
+
+ WITH RECURSIVE foo(i) AS (
+ DELETE FROM t RETURNING i
+ )
+ SELECT i FROM foo ORDER BY i;