Thread: DELETE ... USING
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
Neil Conway <neilc@samurai.com> writes:
> 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.
Of course, the entire reason this didn't happen years ago is that we
couldn't agree on what keyword to use... you sure you want to reopen
that discussion?
I don't think changing UPDATE is a good idea. It's consistent with
SELECT and people are used to it.
You could argue that something like
DELETE FROM target [ { USING | FROM } othertables ] ...
is the best compromise. Those who like consistency can write FROM,
those who don't like "FROM a FROM b" can write something else.
regards, tom lane
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 ...
regards, tom lane
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
[ CC'ing hackers to see if anyone else wants to weigh in ]
Tom Lane wrote:
> Of course, the entire reason this didn't happen years ago is that we
> couldn't agree on what keyword to use... you sure you want to reopen
> that discussion?
Sure, it doesn't seem too difficult to settle to me.
> I don't think changing UPDATE is a good idea. It's consistent with
> SELECT and people are used to it.
Fair enough, I can't get too excited about it either.
> You could argue that something like
>
> DELETE FROM target [ { USING | FROM } othertables ] ...
>
> is the best compromise. Those who like consistency can write FROM,
> those who don't like "FROM a FROM b" can write something else.
This would be fine with me. Are there any other opinions out there on
what syntax would be best for this feature? (For those on -hackers, the
feature in question is adding the ability to specify additional tables
to "join" against in a DELETE, as can be done using FROM in UPDATE.)
-Neil
Hi Neil, > > 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. > Greate job. But I'm worried about add_missing_from enabled. See: euler=# delete from t3 using t1 where b > 500; DELETE 4 euler=# select * from t3; x | y ---+--- (0 rows) In this case, I 'forget' to do the join and it delete all rows from t3. I know that user needs to pay attention, but ... What about default add_missing_from to off? The other case is: euler=# select * from t1 where t1.a = t3.x; NOTICE: adding missing FROM-clause entry for table "t3" NOTICE: adding missing FROM-clause entry for table "t3" a | b ---+---- 5 | 10 (1 row) euler=# delete from t1 where t1.a = t3.x; DELETE 1 euler=# I think we need at least a NOTICE here. Of course it could be extended to UPDATE too. BTW, what about regression tests for UPDATE ... FROM? PS> all examples are extracted from regression database. Euler Taveira de Oliveira euler[at]yahoo_com_br Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/
Euler Taveira de Oliveira wrote: > I'm worried about add_missing_from enabled. The plan is to make add_missing_from default to false in 8.1 > euler=# delete from t3 using t1 where b > 500; > DELETE 4 > euler=# select * from t3; > x | y > ---+--- > (0 rows) > > In this case, I 'forget' to do the join and it delete all rows from t3. > I know that user needs to pay attention, but ... What about default > add_missing_from to off? add_missing_from would not make any difference here. The problem is that there is no join clause between t3 and t1, not that t1 is being implicitly added to the range table (which is what add_missing_from would warn you about). The problem is analogous to a SELECT like: SELECT * FROM t3, t1 WHERE b > 500; i.e. forgetting to specify a join clause and therefore accidentally computing the cartesian product. There has been some gripping recently on -hackers about disabling this or emitting a warning of some kind. > euler=# select * from t1 where t1.a = t3.x; > NOTICE: adding missing FROM-clause entry for table "t3" > NOTICE: adding missing FROM-clause entry for table "t3" > a | b > ---+---- > 5 | 10 > (1 row) > > euler=# delete from t1 where t1.a = t3.x; > DELETE 1 > euler=# > > I think we need at least a NOTICE here. Of course it could be extended > to UPDATE too. I can see an argument for having a NOTICE here. On the other hand, add_missing_from will default to false in 8.1, so presumably the only people enabling it will be those who specifically need backward compatibility for old applications that they cannot afford to change. Filling the logs with bogus NOTICEs would be sufficiently annoying it would probably force some people to modify their applications, thereby defeating the point of having a backward compatibility GUC variable in the first place. > BTW, what about regression tests for UPDATE ... FROM? I agree regression tests would be useful -- you are welcome to send a patch :) -Neil
Neil Conway <neilc@samurai.com> writes:
> Euler Taveira de Oliveira wrote:
>> euler=# delete from t1 where t1.a = t3.x;
>> DELETE 1
>> euler=#
>>
>> I think we need at least a NOTICE here. Of course it could be extended
>> to UPDATE too.
> I can see an argument for having a NOTICE here. On the other hand,
> add_missing_from will default to false in 8.1, ...
... but when it is TRUE, there should be a notice, same as there is in
SELECT. UPDATE should produce such a notice too, IMHO. Probably we
omitted the message originally because there was no way to avoid it
in a DELETE, but now there will be.
regards, tom lane
Tom Lane wrote: > ... but when it is TRUE, there should be a notice, same as there is in > SELECT. UPDATE should produce such a notice too, IMHO. Probably we > omitted the message originally because there was no way to avoid it > in a DELETE, but now there will be. Well, my previous message described why I'm not sure that this line of reasoning is correct. I think the only really proper configuration is add_missing_from=false and an explicit USING/FROM list. Just about the only reason to enable add_missing_from would be for compatibility with previous releases of PostgreSQL -- and that "compatible" behavior is not to issue a warning for UPDATE and DELETE in this situation. If the user deliberately enables add_missing_from, I'm inclined to trust them that they know what they're doing. -Neil
Neil Conway <neilc@samurai.com> writes:
> Well, my previous message described why I'm not sure that this line of
> reasoning is correct. I think the only really proper configuration is
> add_missing_from=false and an explicit USING/FROM list. Just about the
> only reason to enable add_missing_from would be for compatibility with
> previous releases of PostgreSQL -- and that "compatible" behavior is not
> to issue a warning for UPDATE and DELETE in this situation.
Hmm. There's some merit in that position, but consider this: we are
encouraging people rather strongly to move to the add_missing_from=false
behavior. So add_missing_from=true could be seen as a testing situation
in which you'd like to know which of your queries have a problem, while
not actually causing your app to fail. Strict backwards compatibility
won't produce the warning but also won't help you find what will break.
regards, tom lane
Tom Lane wrote: > Hmm. There's some merit in that position, but consider this: we are > encouraging people rather strongly to move to the add_missing_from=false > behavior. So add_missing_from=true could be seen as a testing situation > in which you'd like to know which of your queries have a problem, while > not actually causing your app to fail. Strict backwards compatibility > won't produce the warning but also won't help you find what will break. Hmm, ok, I can see where that would be useful. Looking at how to implement this, there is some rather dodgy code in warnAutoRange() in parse_relation.c that only emits the notice about adding a missing FROM clause entry if the query already has at least one range table entry in its FROM clause. The idea appears to be to not issue warnings about queries like "SELECT foo.*;", but it also means we don't end up warning about DELETE and UPDATE. I think the right fix is to remove the "inFromCl" check, and always issue a notice. With add_missing_from=true, all these queries are rejected anyway, so I think it makes sense to warn about all of them when add_missing_from is disabled. Objections? -Neil
Hi Neil, > Looking at how to implement this, there is some rather dodgy code in > warnAutoRange() in parse_relation.c that only emits the notice about > adding a missing FROM clause entry if the query already has at least > one > range table entry in its FROM clause. The idea appears to be to not > issue warnings about queries like "SELECT foo.*;", but it also means > we > don't end up warning about DELETE and UPDATE. > > I think the right fix is to remove the "inFromCl" check, and always > issue a notice. With add_missing_from=true, all these queries are > rejected anyway, so I think it makes sense to warn about all of them > when add_missing_from is disabled. Objections? > No. That's why I'm thinking now while looking at the code :) Could you provide a patch? Euler Taveira de Oliveira euler[at]yahoo_com_br Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/
Euler Taveira de Oliveira wrote:
> Could you provide a patch?
Sure, a revised patch is attached. Note that this change will also
require updating 25 (!) of the regression tests, since they use the
SELECT-without-FROM syntax. I will update the tests (by adding an
explicit FROM clause) before applying the patch -- which I'll do
tomorrow, barring any objections.
-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 6 Apr 2005 05:26:25 -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 6 Apr 2005 05:26:25 -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 6 Apr 2005 05:26:25 -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 6 Apr 2005 05:26:26 -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 6 Apr 2005 05:26:26 -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/parser/parse_relation.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/parser/parse_relation.c,v
retrieving revision 1.103
diff -c -r1.103 parse_relation.c
*** src/backend/parser/parse_relation.c 31 Mar 2005 22:46:13 -0000 1.103
--- src/backend/parser/parse_relation.c 6 Apr 2005 07:01:09 -0000
***************
*** 596,601 ****
--- 596,602 ----
RangeTblEntry *rte = rt_fetch(varno, pstate->p_rtable);
/* joins are always inFromCl, so no need to check */
+ Assert(rte->inFromCl);
/* use orig_pstate here to get the right sublevels_up */
newresult = scanRTEForColumn(orig_pstate, rte, colname);
***************
*** 1968,1984 ****
/*
* Generate a warning or error about an implicit RTE, if appropriate.
*
! * If ADD_MISSING_FROM is not enabled, raise an error.
! *
! * Our current theory on warnings is that we should allow "SELECT foo.*"
! * but warn about a mixture of explicit and implicit RTEs.
*/
static void
warnAutoRange(ParseState *pstate, RangeVar *relation)
{
- bool foundInFromCl = false;
- ListCell *temp;
-
if (!add_missing_from)
{
if (pstate->parentParseState != NULL)
--- 1969,1980 ----
/*
* Generate a warning or error about an implicit RTE, if appropriate.
*
! * If ADD_MISSING_FROM is not enabled, raise an error. Otherwise, emit
! * a warning.
*/
static void
warnAutoRange(ParseState *pstate, RangeVar *relation)
{
if (!add_missing_from)
{
if (pstate->parentParseState != NULL)
***************
*** 1992,2010 ****
errmsg("missing FROM-clause entry for table \"%s\"",
relation->relname)));
}
!
! foreach(temp, pstate->p_rtable)
! {
! RangeTblEntry *rte = lfirst(temp);
!
! if (rte->inFromCl)
! {
! foundInFromCl = true;
! break;
! }
! }
! if (foundInFromCl)
{
if (pstate->parentParseState != NULL)
ereport(NOTICE,
(errcode(ERRCODE_UNDEFINED_TABLE),
--- 1988,1996 ----
errmsg("missing FROM-clause entry for table \"%s\"",
relation->relname)));
}
! else
{
+ /* just issue a warning */
if (pstate->parentParseState != NULL)
ereport(NOTICE,
(errcode(ERRCODE_UNDEFINED_TABLE),
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /Users/neilc/local/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 6 Apr 2005 05:26:26 -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: /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 6 Apr 2005 05:26:26 -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 6 Apr 2005 05:26:26 -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 6 Apr 2005 05:26:26 -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 6 Apr 2005 05:26:26 -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 6 Apr 2005 05:26:26 -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
Neil Conway wrote: > Sure, a revised patch is attached. Note that this change will also > require updating 25 (!) of the regression tests, since they use the > SELECT-without-FROM syntax. I've applied the attached patch to HEAD. Due to the widespread updates to the regression tests, the tests for some platforms may be (trivially) broken, despite my efforts to make the necessary updates. If that's the case, please let me know. -Neil
Attachment
Neil Conway wrote:
> Euler Taveira de Oliveira wrote:
> > Could you provide a patch?
>
> Sure, a revised patch is attached. Note that this change will also
> require updating 25 (!) of the regression tests, since they use the
> SELECT-without-FROM syntax. I will update the tests (by adding an
> explicit FROM clause) before applying the patch -- which I'll do
> tomorrow, barring any objections.
I just checked current CVS and see exactly what you describe:
test=> SELECT pg_class.* LIMIT 0;
ERROR: missing FROM-clause entry for table "pg_class"
test=> SET add_missing_from=true;
SET
test=> SELECT pg_class.* LIMIT 0;
NOTICE: adding missing FROM-clause entry for table "pg_class"
Is this what we want? I don't think so. I thought we wanted to
maintain the backward-compatible syntax of no FROM clause.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote: > Is this what we want? I don't think so. I thought we wanted to > maintain the backward-compatible syntax of no FROM clause. We do? Why? It is just as noncompliant with the SQL spec as other variants of this behavior. add_missing_from would *always* have rejected those queries, so ISTM we have been discouraging this case for as long as add_missing_from has existed. If we want to allow this syntax by default, we will need to effectively redefine the meaning of add_missing_from -- which is fine, I just didn't think anyone wanted that. -Neil
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> test=> SELECT pg_class.* LIMIT 0;
> NOTICE: adding missing FROM-clause entry for table "pg_class"
> Is this what we want? I don't think so. I thought we wanted to
> maintain the backward-compatible syntax of no FROM clause.
Well, the discussion earlier in the week concluded that
add_missing_from=true should emit a notice in every case where
add_missing_from=false would fail. Do you want to argue against
that conclusion?
regards, tom lane