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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
