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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
