Re: DELETE ... USING

Tom Lane wrote:
> BTW, this patch is lacking ruleutils.c support.  Put a DELETE USING
> into a rule and see whether pg_dump will dump the rule correctly ...

Good catch; a revised patch is attached.

-Neil
Index: doc/src/sgml/ref/delete.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql/doc/src/sgml/ref/delete.sgml,v
retrieving revision 1.22
diff -c -r1.22 delete.sgml
*** doc/src/sgml/ref/delete.sgml    9 Jan 2005 05:57:45 -0000    1.22
--- doc/src/sgml/ref/delete.sgml    5 Apr 2005 00:42:17 -0000
***************
*** 20,26 ****

   <refsynopsisdiv>
  <synopsis>
! DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ WHERE <replaceable
class="PARAMETER">condition</replaceable>] 
  </synopsis>
   </refsynopsisdiv>

--- 20,28 ----

   <refsynopsisdiv>
  <synopsis>
! DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable>
!     [ USING <replaceable class="PARAMETER">usinglist</replaceable> ]
!     [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
  </synopsis>
   </refsynopsisdiv>

***************
*** 50,58 ****
    </para>

    <para>
     You must have the <literal>DELETE</literal> privilege on the table
     to delete from it, as well as the <literal>SELECT</literal>
!    privilege for any table whose values are read in the <replaceable
     class="parameter">condition</replaceable>.
    </para>
   </refsect1>
--- 52,69 ----
    </para>

    <para>
+    There are two ways to delete rows in a table using information
+    contained in other tables in the database: using sub-selects, or
+    specifying additional tables in the <literal>USING</literal> clause.
+    Which technique is more appropriate depends on the specific
+    circumstances.
+   </para>
+
+   <para>
     You must have the <literal>DELETE</literal> privilege on the table
     to delete from it, as well as the <literal>SELECT</literal>
!    privilege for any table in the <literal>USING</literal> clause or
!    whose values are read in the <replaceable
     class="parameter">condition</replaceable>.
    </para>
   </refsect1>
***************
*** 71,76 ****
--- 82,101 ----
     </varlistentry>

     <varlistentry>
+     <term><replaceable class="PARAMETER">usinglist</replaceable></term>
+     <listitem>
+      <para>
+       A list of table expressions, allowing columns from other tables
+       to appear in the <literal>WHERE</> condition.  This is similar
+       to the list of tables that can be specified in the <xref
+       linkend="sql-from" endterm="sql-from-title"> of a
+       <command>SELECT</command> statement; for example, an alias for
+       the table name can be specified.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
      <term><replaceable class="parameter">condition</replaceable></term>
      <listitem>
       <para>
***************
*** 105,114 ****

    <para>
     <productname>PostgreSQL</productname> lets you reference columns of
!    other tables in the <literal>WHERE</> condition.  For example, to
!    delete all films produced by a given producer, one might do
  <programlisting>
! DELETE FROM films
    WHERE producer_id = producers.id AND producers.name = 'foo';
  </programlisting>
     What is essentially happening here is a join between <structname>films</>
--- 130,140 ----

    <para>
     <productname>PostgreSQL</productname> lets you reference columns of
!    other tables in the <literal>WHERE</> condition by specifying the
!    other tables in the <literal>USING</literal> clause.  For example,
!    to delete all films produced by a given producer, one might do
  <programlisting>
! DELETE FROM films USING producers
    WHERE producer_id = producers.id AND producers.name = 'foo';
  </programlisting>
     What is essentially happening here is a join between <structname>films</>
***************
*** 120,129 ****
    WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
  </programlisting>
     In some cases the join style is easier to write or faster to
!    execute than the sub-select style.  One objection to the join style
!    is that there is no explicit list of what tables are being used,
!    which makes the style somewhat error-prone; also it cannot handle
!    self-joins.
    </para>
   </refsect1>

--- 146,158 ----
    WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
  </programlisting>
     In some cases the join style is easier to write or faster to
!    execute than the sub-select style.
!   </para>
!
!   <para>
!    If <varname>add_missing_from</varname> is enabled, any relations
!    mentioned in the <literal>WHERE</literal> condition will be
!    implicitly added to the <literal>USING</literal> clause.
    </para>
   </refsect1>

***************
*** 149,157 ****
    <title>Compatibility</title>

    <para>
!    This command conforms to the SQL standard, except that the ability to
!    reference other tables in the <literal>WHERE</> clause is a
!    <productname>PostgreSQL</productname> extension.
    </para>
   </refsect1>
  </refentry>
--- 178,187 ----
    <title>Compatibility</title>

    <para>
!    This command conforms to the SQL standard, except that the
!    <literal>USING</> clause and the ability to reference other tables
!    in the <literal>WHERE</> clause are <productname>PostgreSQL</>
!    extensions.
    </para>
   </refsect1>
  </refentry>
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.299
diff -c -r1.299 copyfuncs.c
*** src/backend/nodes/copyfuncs.c    29 Mar 2005 17:58:50 -0000    1.299
--- src/backend/nodes/copyfuncs.c    5 Apr 2005 00:42:17 -0000
***************
*** 1578,1583 ****
--- 1578,1584 ----

      COPY_NODE_FIELD(relation);
      COPY_NODE_FIELD(whereClause);
+     COPY_NODE_FIELD(usingClause);

      return newnode;
  }
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.238
diff -c -r1.238 equalfuncs.c
*** src/backend/nodes/equalfuncs.c    29 Mar 2005 17:58:50 -0000    1.238
--- src/backend/nodes/equalfuncs.c    5 Apr 2005 00:42:17 -0000
***************
*** 685,690 ****
--- 685,691 ----
  {
      COMPARE_NODE_FIELD(relation);
      COMPARE_NODE_FIELD(whereClause);
+     COMPARE_NODE_FIELD(usingClause);

      return true;
  }
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.316
diff -c -r1.316 analyze.c
*** src/backend/parser/analyze.c    10 Mar 2005 23:21:23 -0000    1.316
--- src/backend/parser/analyze.c    5 Apr 2005 00:42:17 -0000
***************
*** 482,487 ****
--- 482,495 ----

      qry->distinctClause = NIL;

+     /*
+      * The USING clause is non-standard SQL syntax, and is equivalent
+      * in functionality to the FROM list that can be specified for
+      * UPDATE. The USING keyword is used rather than FROM because FROM
+      * is already a keyword in the DELETE syntax.
+      */
+     transformFromClause(pstate, stmt->usingClause);
+
      /* fix where clause */
      qual = transformWhereClause(pstate, stmt->whereClause, "WHERE");

Index: src/backend/parser/gram.y
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.486
diff -c -r2.486 gram.y
*** src/backend/parser/gram.y    31 Mar 2005 22:46:11 -0000    2.486
--- src/backend/parser/gram.y    5 Apr 2005 00:42:17 -0000
***************
*** 229,235 ****
                  transaction_mode_list_or_empty
                  TableFuncElementList
                  prep_type_clause prep_type_list
!                 execute_param_clause

  %type <range>    into_clause OptTempTableName

--- 229,235 ----
                  transaction_mode_list_or_empty
                  TableFuncElementList
                  prep_type_clause prep_type_list
!                 execute_param_clause using_clause

  %type <range>    into_clause OptTempTableName

***************
*** 4734,4748 ****
   *
   *****************************************************************************/

! DeleteStmt: DELETE_P FROM relation_expr where_clause
                  {
                      DeleteStmt *n = makeNode(DeleteStmt);
                      n->relation = $3;
!                     n->whereClause = $4;
                      $$ = (Node *)n;
                  }
          ;

  LockStmt:    LOCK_P opt_table qualified_name_list opt_lock opt_nowait
                  {
                      LockStmt *n = makeNode(LockStmt);
--- 4734,4754 ----
   *
   *****************************************************************************/

! DeleteStmt: DELETE_P FROM relation_expr using_clause where_clause
                  {
                      DeleteStmt *n = makeNode(DeleteStmt);
                      n->relation = $3;
!                     n->usingClause = $4;
!                     n->whereClause = $5;
                      $$ = (Node *)n;
                  }
          ;

+ using_clause:
+                 USING from_list                        { $$ = $2; }
+             | /*EMPTY*/                                { $$ = NIL; }
+         ;
+
  LockStmt:    LOCK_P opt_table qualified_name_list opt_lock opt_nowait
                  {
                      LockStmt *n = makeNode(LockStmt);
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.189
diff -c -r1.189 ruleutils.c
*** src/backend/utils/adt/ruleutils.c    29 Mar 2005 00:17:08 -0000    1.189
--- src/backend/utils/adt/ruleutils.c    5 Apr 2005 00:48:40 -0000
***************
*** 199,205 ****
  static void get_agg_expr(Aggref *aggref, deparse_context *context);
  static void get_const_expr(Const *constval, deparse_context *context);
  static void get_sublink_expr(SubLink *sublink, deparse_context *context);
! static void get_from_clause(Query *query, deparse_context *context);
  static void get_from_clause_item(Node *jtnode, Query *query,
                       deparse_context *context);
  static void get_from_clause_alias(Alias *alias, int varno,
--- 199,206 ----
  static void get_agg_expr(Aggref *aggref, deparse_context *context);
  static void get_const_expr(Const *constval, deparse_context *context);
  static void get_sublink_expr(SubLink *sublink, deparse_context *context);
! static void get_from_clause(Query *query, const char *prefix,
!                             deparse_context *context);
  static void get_from_clause_item(Node *jtnode, Query *query,
                       deparse_context *context);
  static void get_from_clause_alias(Alias *alias, int varno,
***************
*** 2021,2027 ****
      }

      /* Add the FROM clause if needed */
!     get_from_clause(query, context);

      /* Add the WHERE clause if given */
      if (query->jointree->quals != NULL)
--- 2022,2028 ----
      }

      /* Add the FROM clause if needed */
!     get_from_clause(query, " FROM ", context);

      /* Add the WHERE clause if given */
      if (query->jointree->quals != NULL)
***************
*** 2326,2332 ****
      }

      /* Add the FROM clause if needed */
!     get_from_clause(query, context);

      /* Finally add a WHERE clause if given */
      if (query->jointree->quals != NULL)
--- 2327,2333 ----
      }

      /* Add the FROM clause if needed */
!     get_from_clause(query, " FROM ", context);

      /* Finally add a WHERE clause if given */
      if (query->jointree->quals != NULL)
***************
*** 2362,2367 ****
--- 2363,2371 ----
                       only_marker(rte),
                       generate_relation_name(rte->relid));

+     /* Add the USING clause if given */
+     get_from_clause(query, " USING ", context);
+
      /* Add a WHERE clause if given */
      if (query->jointree->quals != NULL)
      {
***************
*** 3806,3815 ****

  /* ----------
   * get_from_clause            - Parse back a FROM clause
   * ----------
   */
  static void
! get_from_clause(Query *query, deparse_context *context)
  {
      StringInfo    buf = context->buf;
      bool        first = true;
--- 3810,3823 ----

  /* ----------
   * get_from_clause            - Parse back a FROM clause
+  *
+  * "prefix" is the keyword that denotes the start of the list of FROM
+  * elements. It is FROM when used to parse back SELECT and UPDATE, but
+  * is USING when parsing back DELETE.
   * ----------
   */
  static void
! get_from_clause(Query *query, const char *prefix, deparse_context *context)
  {
      StringInfo    buf = context->buf;
      bool        first = true;
***************
*** 3841,3847 ****

          if (first)
          {
!             appendContextKeyword(context, " FROM ",
                                   -PRETTYINDENT_STD, PRETTYINDENT_STD, 2);
              first = false;
          }
--- 3849,3855 ----

          if (first)
          {
!             appendContextKeyword(context, prefix,
                                   -PRETTYINDENT_STD, PRETTYINDENT_STD, 2);
              first = false;
          }
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/bin/psql/tab-complete.c,v
retrieving revision 1.123
diff -c -r1.123 tab-complete.c
*** src/bin/psql/tab-complete.c    4 Apr 2005 07:19:44 -0000    1.123
--- src/bin/psql/tab-complete.c    5 Apr 2005 00:42:17 -0000
***************
*** 1164,1173 ****
      else if (pg_strcasecmp(prev2_wd, "DELETE") == 0 &&
               pg_strcasecmp(prev_wd, "FROM") == 0)
          COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
!     /* Complete DELETE FROM <table> with "WHERE" (perhaps a safe idea?) */
      else if (pg_strcasecmp(prev3_wd, "DELETE") == 0 &&
               pg_strcasecmp(prev2_wd, "FROM") == 0)
!         COMPLETE_WITH_CONST("WHERE");

  /* EXPLAIN */

--- 1164,1179 ----
      else if (pg_strcasecmp(prev2_wd, "DELETE") == 0 &&
               pg_strcasecmp(prev_wd, "FROM") == 0)
          COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
!     /* Complete DELETE FROM <table> */
      else if (pg_strcasecmp(prev3_wd, "DELETE") == 0 &&
               pg_strcasecmp(prev2_wd, "FROM") == 0)
!     {
!         static const char *const list_DELETE[] =
!         {"USING", "WHERE", "SET", NULL};
!
!         COMPLETE_WITH_LIST(list_DELETE);
!     }
!     /* XXX: implement tab completion for DELETE ... USING */

  /* EXPLAIN */

Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.275
diff -c -r1.275 parsenodes.h
*** src/include/nodes/parsenodes.h    29 Mar 2005 17:58:51 -0000    1.275
--- src/include/nodes/parsenodes.h    5 Apr 2005 00:42:17 -0000
***************
*** 613,618 ****
--- 613,619 ----
      NodeTag        type;
      RangeVar   *relation;        /* relation to delete from */
      Node       *whereClause;    /* qualifications */
+     List       *usingClause;    /* optional using clause for more tables */
  } DeleteStmt;

  /* ----------------------
Index: src/test/regress/expected/join.out
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/join.out,v
retrieving revision 1.23
diff -c -r1.23 join.out
*** src/test/regress/expected/join.out    24 Mar 2005 19:14:49 -0000    1.23
--- src/test/regress/expected/join.out    5 Apr 2005 00:42:17 -0000
***************
*** 2147,2149 ****
--- 2147,2186 ----
  DROP TABLE t3;
  DROP TABLE J1_TBL;
  DROP TABLE J2_TBL;
+ -- Both DELETE and UPDATE allow the specification of additional tables
+ -- to "join" against to determine which rows should be modified.
+ CREATE TEMP TABLE t1 (a int, b int);
+ CREATE TEMP TABLE t2 (a int, b int);
+ CREATE TEMP TABLE t3 (x int, y int);
+ INSERT INTO t1 VALUES (5, 10);
+ INSERT INTO t1 VALUES (15, 20);
+ INSERT INTO t1 VALUES (100, 100);
+ INSERT INTO t1 VALUES (200, 1000);
+ INSERT INTO t2 VALUES (200, 2000);
+ INSERT INTO t3 VALUES (5, 20);
+ INSERT INTO t3 VALUES (6, 7);
+ INSERT INTO t3 VALUES (7, 8);
+ INSERT INTO t3 VALUES (500, 100);
+ DELETE FROM t3 USING t1 table1 WHERE t3.x = table1.a;
+ SELECT * FROM t3;
+   x  |  y
+ -----+-----
+    6 |   7
+    7 |   8
+  500 | 100
+ (3 rows)
+
+ DELETE FROM t3 USING t1 JOIN t2 USING (a) WHERE t3.x > t1.a;
+ SELECT * FROM t3;
+  x | y
+ ---+---
+  6 | 7
+  7 | 8
+ (2 rows)
+
+ DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y;
+ SELECT * FROM t3;
+  x | y
+ ---+---
+ (0 rows)
+
Index: src/test/regress/expected/join_1.out
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/join_1.out,v
retrieving revision 1.3
diff -c -r1.3 join_1.out
*** src/test/regress/expected/join_1.out    26 Mar 2005 03:38:01 -0000    1.3
--- src/test/regress/expected/join_1.out    5 Apr 2005 00:42:17 -0000
***************
*** 2147,2149 ****
--- 2147,2185 ----
  DROP TABLE t3;
  DROP TABLE J1_TBL;
  DROP TABLE J2_TBL;
+ -- Both DELETE and UPDATE allow the specification of additional tables
+ -- to "join" against to determine which rows should be modified.
+ CREATE TEMP TABLE t1 (a int, b int);
+ CREATE TEMP TABLE t2 (a int, b int);
+ CREATE TEMP TABLE t3 (x int, y int);
+ INSERT INTO t1 VALUES (5, 10);
+ INSERT INTO t1 VALUES (15, 20);
+ INSERT INTO t1 VALUES (100, 100);
+ INSERT INTO t1 VALUES (200, 1000);
+ INSERT INTO t2 VALUES (200, 2000);
+ INSERT INTO t3 VALUES (5, 20);
+ INSERT INTO t3 VALUES (6, 7);
+ INSERT INTO t3 VALUES (7, 8);
+ INSERT INTO t3 VALUES (500, 100);
+ DELETE FROM t3 USING t1 table1 WHERE t3.x = table1.a;
+ SELECT * FROM t3;
+   x  |  y
+ -----+-----
+    6 |   7
+    7 |   8
+  500 | 100
+ (3 rows)
+
+ DELETE FROM t3 USING t1 JOIN t2 USING (a) WHERE t3.x > t1.a;
+ SELECT * FROM t3;
+  x | y
+ ---+---
+  6 | 7
+  7 | 8
+ (2 rows)
+
+ DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y;
+ SELECT * FROM t3;
+  x | y
+ ---+---
+ (0 rows)
Index: src/test/regress/sql/join.sql
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/sql/join.sql,v
retrieving revision 1.15
diff -c -r1.15 join.sql
*** src/test/regress/sql/join.sql    3 Dec 2004 22:19:28 -0000    1.15
--- src/test/regress/sql/join.sql    5 Apr 2005 00:42:17 -0000
***************
*** 349,351 ****
--- 349,375 ----

  DROP TABLE J1_TBL;
  DROP TABLE J2_TBL;
+
+ -- Both DELETE and UPDATE allow the specification of additional tables
+ -- to "join" against to determine which rows should be modified.
+
+ CREATE TEMP TABLE t1 (a int, b int);
+ CREATE TEMP TABLE t2 (a int, b int);
+ CREATE TEMP TABLE t3 (x int, y int);
+
+ INSERT INTO t1 VALUES (5, 10);
+ INSERT INTO t1 VALUES (15, 20);
+ INSERT INTO t1 VALUES (100, 100);
+ INSERT INTO t1 VALUES (200, 1000);
+ INSERT INTO t2 VALUES (200, 2000);
+ INSERT INTO t3 VALUES (5, 20);
+ INSERT INTO t3 VALUES (6, 7);
+ INSERT INTO t3 VALUES (7, 8);
+ INSERT INTO t3 VALUES (500, 100);
+
+ DELETE FROM t3 USING t1 table1 WHERE t3.x = table1.a;
+ SELECT * FROM t3;
+ DELETE FROM t3 USING t1 JOIN t2 USING (a) WHERE t3.x > t1.a;
+ SELECT * FROM t3;
+ DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y;
+ SELECT * FROM t3;
\ No newline at end of file