Re: [GENERAL] Using results from DELETE ... RETURNING

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;