DELETE ... USING - Mailing list pgsql-patches

This patch is a cleaned up version of Euler Taveira de Oliveira's patch
implementing DELETE ... USING. I removed a bunch of unused code (no need
to tlist transformations), updated copyfuncs/equalfuncs, improved the
documentation, rearranged a few things, and added regression tests. I
haven't done psql tab completion. Barring any objections, I'll apply
this to HEAD tomorrow.

On a related note, UPDATE uses the FROM keyword to denote the list of
relations to join with, whereas DELETE uses USING. Should we make USING
an alias for FROM in UPDATE and if so, should we deprecate FROM? This
would be more consistent, which I suppose is a good thing.

-Neil
Index: doc/src/sgml/ref/delete.sgml
===================================================================
RCS file: /Users/neilc/local/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    4 Apr 2005 10:10:42 -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: /Users/neilc/local/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    4 Apr 2005 07:56:36 -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: /Users/neilc/local/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    4 Apr 2005 07:56:36 -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: /Users/neilc/local/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    4 Apr 2005 07:56:36 -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: /Users/neilc/local/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    4 Apr 2005 07:56:36 -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/bin/psql/tab-complete.c
===================================================================
RCS file: /Users/neilc/local/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    4 Apr 2005 10:07:21 -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: /Users/neilc/local/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    4 Apr 2005 07:56:36 -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: /Users/neilc/local/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    4 Apr 2005 07:56:36 -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: /Users/neilc/local/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    4 Apr 2005 07:56:36 -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: /Users/neilc/local/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    4 Apr 2005 07:56:36 -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