8000 Add a "USING" clause to DELETE, which is equivalent to the FROM clause · home201448/postgres@f5ab0a1 · GitHub
[go: up one dir, main page]
Skip to content

Commit f5ab0a1

Browse files
author
Neil Conway
committed
Add a "USING" clause to DELETE, which is equivalent to the FROM clause
in UPDATE. We also now issue a NOTICE if a query has _any_ implicit range table entries -- in the past, we would only warn about implicit RTEs in SELECTs with at least one explicit RTE. As a result of the warning change, 25 of the regression tests had to be updated. I also took the opportunity to remove some bogus whitespace differences between some of the float4 and float8 variants. I believe I have correctly updated all the platform-specific variants, but let me know if that's not the case. Original patch for DELETE ... USING from Euler Taveira de Oliveira, reworked by Neil Conway.
1 parent be2f825 commit f5ab0a1

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

68 files changed

+393
-234
lines changed

‎doc/src/sgml/ref/delete.sgml

Lines changed: 43 additions & 13 deletions
10000
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.22 2005/01/09 05:57:45 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.23 2005/04/07 01:51:37 neilc Exp $
33
PostgreSQL documentation
44
-->
55

@@ -20,7 +20,9 @@ PostgreSQL documentation
2020

2121
<refsynopsisdiv>
2222
<synopsis>
23-
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
23+
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable>
24+
[ USING <replaceable class="PARAMETER">usinglist</replaceable> ]
25+
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
2426
</synopsis>
2527
</refsynopsisdiv>
2628

@@ -49,10 +51,19 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ WHERE
4951
<literal>ONLY</literal> clause.
5052
</para>
5153

54+
<para>
55+
There are two ways to delete rows in a table using information
56+
contained in other tables in the database: using sub-selects, or
57+
specifying additional tables in the <literal>USING</literal> clause.
58+
Which technique is more appropriate depends on the specific
59+
circumstances.
60+
</para>
61+
5262
<para>
5363
You must have the <literal>DELETE</literal> privilege on the table
5464
to delete from it, as well as the <literal>SELECT</literal>
55-
privilege for any table whose values are read in the <replaceable
65+
privilege for any table in the <literal>USING</literal> clause or
66+
whose values are read in the <replaceable
5667
class="parameter">condition</replaceable>.
5768
</para>
5869
</refsect1>
@@ -70,6 +81,20 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ WHERE
7081
</listitem>
7182
</varlistentry>
7283

84+
<varlistentry>
85+
<term><replaceable class="PARAMETER">usinglist</replaceable></term>
86+
<listitem>
87+
<para>
88+
A list of table expressions, allowing columns from other tables
89+
to appear in the <literal>WHERE</> condition. This is similar
90+
to the list of tables that can be specified in the <xref
91+
linkend="sql-from" endterm="sql-from-title"> of a
92+
<command>SELECT</command> statement; for example, an alias for
93+
the table name can be specified.
94+
</para>
95+
</listitem>
96+
</varlistentry>
97+
7398
<varlistentry>
7499
<term><replaceable class="parameter">condition</replaceable></term>
75100
<listitem>
@@ -105,10 +130,11 @@ DELETE <replaceable class="parameter">count</replaceable>
105130

106131
<para>
107132
<productname>PostgreSQL</productname> lets you reference columns of
108-
other tables in the <literal>WHERE</> condition. For example, to
109-
delete all films produced by a given producer, one might do
133+
other tables in the <literal>WHERE</> condition by specifying the
134+
other tables in the <literal>USING</literal> clause. For example,
135+
to delete all films produced by a given producer, one might do
110136
<programlisting>
111-
DELETE FROM films
137+
DELETE FROM films USING producers
112138
WHERE producer_id = producers.id AND producers.name = 'foo';
113139
</programlisting>
114140
What is essentially happening here is a join between <structname>films</>
@@ -120,10 +146,13 @@ DELETE FROM films
120146
WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
121147
</programlisting>
122148
In some cases the join style is easier to write or faster to
123-
execute than the sub-select style. One objection to the join style
124-
is that there is no explicit list of what tables are being used,
125-
which makes the style somewhat error-prone; also it cannot handle
126-
self-joins.
149+
execute than the sub-select style.
150+
</para>
151+
152+
<para>
153+
If <varname>add_missing_from</varname> is enabled, any relations
154+
mentioned in the <literal>WHERE</literal> condition will be
155+
implicitly added to the <literal>USING</literal> clause.
127156
</para>
128157
</refsect1>
129158

@@ -149,9 +178,10 @@ DELETE FROM films;
149178
<title>Compatibility</title>
150179

151180
<para>
152-
This command conforms to the SQL standard, except that the ability to
153-
reference other tables in the <literal>WHERE</> clause is a
154-
<productname>PostgreSQL</productname> extension.
181+
This command conforms to the SQL standard, except that the
182+
<literal>USING</> clause and the ability to reference other tables
183+
in the <literal>WHERE</> clause are <productname>PostgreSQL</>
184+
extensions.
155185
</para>
156186
</refsect1>
157187
</refentry>

‎src/backend/nodes/copyfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.300 2005/04/06 16:34:05 tgl Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.301 2005/04/07 01:51:38 neilc Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -1563,6 +1563,7 @@ _copyDeleteStmt(DeleteStmt *from)
15631563

15641564
COPY_NODE_FIELD(relation);
15651565
COPY_NODE_FIELD(whereClause);
1566+
COPY_NODE_FIELD(usingClause);
15661567

15671568
return newnode;
15681569
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@
1818
* Portions Copyright (c) 1994, Regents of the University of California
1919
*
2020
* IDENTIFICATI 10000 ON
21-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.239 2005/04/06 16:34:05 tgl Exp $
21+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.240 2005/04/07 01:51:38 neilc Exp $
2222
*
2323
*-------------------------------------------------------------------------
2424
*/
@@ -675,6 +675,7 @@ _equalDeleteStmt(DeleteStmt *a, DeleteStmt *b)
675675
{
676676
COMPARE_NODE_FIELD(relation);
677677
COMPARE_NODE_FIELD(whereClause);
678+
COMPARE_NODE_FIELD(usingClause);
678679

679680
return true;
680681
}

‎src/backend/parser/analyze.c

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.317 2005/04/06 16:34:06 tgl Exp $
9+
* $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.318 2005/04/07 01:51:38 neilc Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -479,6 +479,14 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
479479

480480
qry->distinctClause = NIL;
481481

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

‎src/backend/parser/gram.y

Lines changed: 10 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.486 2005/03/31 22:46:11 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.487 2005/04/07 01:51:38 neilc Exp $
1515
*
1616
* HISTORY
1717
* AUTHOR DATE MAJOR EVENT
@@ -229,7 +229,7 @@ static void doNegateFloat(Value *v);
229229
transaction_mode_list_or_empty
230230
TableFuncElementList
231231
prep_type_clause prep_type_list
232-
execute_param_clause
232+
execute_param_clause using_clause
233233

234234
%type <range> into_clause OptTempTableName
235235

@@ -4734,15 +4734,21 @@ insert_column_item:
47344734
*
47354735
*****************************************************************************/
47364736

4737-
DeleteStmt: DELETE_P FROM relation_expr where_clause
4737+
DeleteStmt: DELETE_P FROM relation_expr using_clause where_clause
47384738
{
47394739
DeleteStmt *n = makeNode(DeleteStmt);
47404740
n->relation = $3;
4741-
n->whereClause = $4;
4741+
n->usingClause = $4;
4742+
n->whereClause = $5;
47424743
$$ = (Node *)n;
47434744
}
47444745
;
47454746

4747+
using_clause:
4748+
USING from_list { $$ = $2; }
4749+
| /*EMPTY*/ { $$ = NIL; }
4750+
;
4751+
47464752
LockStmt: LOCK_P opt_table qualified_name_list opt_lock opt_nowait
47474753
{
47484754
LockStmt *n = makeNode(LockStmt);

‎src/backend/parser/parse_relation.c

Lines changed: 6 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_relation.c,v 1.104 2005/04/06 16:34:06 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_relation.c,v 1.105 2005/04/07 01:51:39 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -596,6 +596,7 @@ colNameToVar(ParseState *pstate, char *colname, bool localonly)
596596
RangeTblEntry *rte = rt_fetch(varno, pstate->p_rtable);
597597

598598
/* joins are always inFromCl, so no need to check */
599+
Assert(rte->inFromCl);
599600

600601
/* use orig_pstate here to get the right sublevels_up */
601602
newresult = scanRTEForColumn(orig_pstate, rte, colname);
@@ -1966,17 +1967,12 @@ attnumTypeId(Relation rd, int attid)
19661967
/*
19671968
* Generate a warning or error about an implicit RTE, if appropriate.
19681969
*
1969-
* If ADD_MISSING_FROM is not enabled, raise an error.
1970-
*
1971-
* Our current theory on warnings is that we should allow "SELECT foo.*"
1972-
* but warn about a mixture of explicit and implicit RTEs.
1970+
* If ADD_MISSING_FROM is not enabled, raise an error. Otherwise, emit
1971+
* a warning.
19731972
*/
19741973
static void
19751974
warnAutoRange(ParseState *pstate, RangeVar *relation)
19761975
{
1977-
bool foundInFromCl = false;
1978-
ListCell *temp;
1979-
19801976
if (!add_missing_from)
19811977
{
19821978
if (pstate->parentParseState != NULL)
@@ -1990,19 +1986,9 @@ warnAutoRange(ParseState *pstate, RangeVar *relation)
19901986
errmsg("missing FROM-clause entry for table \"%s\"",
19911987
relation->relname)));
19921988
}
1993-
1994-
foreach(temp, pstate->p_rtable)
1995-
{
1996-
RangeTblEntry *rte = lfirst(temp);
1997-
1998-
if (rte->inFromCl)
1999-
{
2000-
foundInFromCl = true;
2001-
break;
2002-
}
2003-
}
2004-
if (foundInFromCl)
1989+
else
20051990
{
1991+
/* just issue a warning */
20061992
if (pstate->parentParseState != NULL)
20071993
ereport(NOTICE,
20081994
(errcode(ERRCODE_UNDEFINED_TABLE),

‎src/backend/utils/adt/ruleutils.c

Lines changed: 14 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* back to source text
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.190 2005/04/06 16:34:06 tgl Exp $
6+
* $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.191 2005/04/07 01:51:39 neilc Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -199,7 +199,8 @@ static void get_func_expr(FuncExpr *expr, deparse_context *context,
199199
static void get_agg_expr(Aggref *aggref, deparse_context *context);
200200
static void get_const_expr(Const *constval, deparse_context *context);
201201
static void get_sublink_expr(SubLink *sublink, deparse_context *context);
202-
static void get_from_clause(Query *query, deparse_context *context);
202+
static void get_from_clause(Query *query, const char *prefix,
203+
deparse_context *context);
203204
static void get_from_clause_item(Node *jtnode, Query *query,
204205
deparse_context *context);
205206
static void get_from_clause_alias(Alias *alias, int varno,
@@ -2020,7 +2021,7 @@ get_basic_select_query(Query *query, deparse_context *context,
20202021
}
20212022

20222023
/* Add the FROM clause if needed */
2023-
get_from_clause(query, context);
2024+
get_from_clause(query, " FROM ", context);
20242025

20252026
/* Add the WHERE clause if given */
20262027
if (query->jointree->quals != NULL)
@@ -2325,7 +2326,7 @@ get_update_query_def(Query *query, deparse_context *context)
23252326
}
23262327

23272328
/* Add the FROM clause if needed */
2328-
get_from_clause(query, context);
2329+
get_from_clause(query, " FROM ", context);
23292330

23302331
/* Finally add a WHERE clause if given */
23312332
if (query->jointree->quals != NULL)
@@ -2361,6 +2362,9 @@ get_delete_query_def(Query *query, deparse_context *context)
23612362
only_marker(rte),
23622363
generate_relation_name(rte->relid));
23632364

2365+
/* Add the USING clause if given */
2366+
get_from_clause(query, " USING ", context);
2367+
23642368
/* Add a WHERE clause if given */
23652369
if (query->jointree->quals != NULL)
23662370
{
@@ -3805,10 +3809,14 @@ get_sublink_expr(SubLink *sublink, deparse_context *context)
38053809

38063810
/* ----------
38073811
* get_from_clause - Parse back a FROM clause
3812+
*
3813+
* "prefix" is the keyword that denotes the start of the list of FROM
3814+
* elements. It is FROM when used to parse back SELECT and UPDATE, but
3815+
* is USING when parsing back DELETE.
38083816
* ----------
38093817
*/
38103818
static void
3811-
get_from_clause(Query *query, deparse_context *context)
3819+
get_from_clause(Query *query, const char *prefix, deparse_context *context)
38123820
{
38133821
StringInfo buf = context->buf;
38143822
bool first = true;
@@ -3840,7 +3848,7 @@ get_from_clause(Query *query, deparse_context *context)
38403848

38413849
if (first)
38423850
{
3843-
appendContextKeyword(context, " FROM ",
3851+
appendContextKeyword(context, prefix,
38443852
-PRETTYINDENT_STD, PRETTYINDENT_STD, 2);
38453853
first = false;
38463854
}

‎src/bin/psql/tab-complete.c

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 2000-2005, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.123 2005/04/04 07:19:44 neilc Exp $
6+
* $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.124 2005/04/07 01:51:39 neilc Exp $
77
*/
88

99
/*----------------------------------------------------------------------
@@ -1164,10 +1164,16 @@ psql_completion(char *text, int start, int end)
11641164
else if (pg_strcasecmp(prev2_wd, "DELETE") == 0 &&
11651165
pg_strcasecmp(prev_wd, "FROM") == 0)
11661166
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
1167-
/* Complete DELETE FROM <table> with "WHERE" (perhaps a safe idea?) */
1167+
/* Complete DELETE FROM <table> */
11681168
else if (pg_strcasecmp(prev3_wd, "DELETE") == 0 &&
11691169
pg_strcasecmp(prev2_wd, "FROM") == 0)
1170-
COMPLETE_WITH_CONST("WHERE");
1170+
{
1171+
static const char *const list_DELETE[] =
1172+
{"USING", "WHERE", "SET", NULL};
1173+
1174+
COMPLETE_WITH_LIST(list_DELETE);
1175+
}
1176+
/* XXX: implement tab completion for DELETE ... USING */
11711177

11721178
/* EXPLAIN */
11731179

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.276 2005/04/06 16:34:07 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.277 2005/04/07 01:51:40 neilc Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -613,6 +613,7 @@ typedef struct DeleteStmt
613613
NodeTag type;
614614
RangeVar *relation; /* relation to delete from */
615615
Node *whereClause; /* qualifications */
616+
List *usingClause; /* optional using clause for more tables */
616617
} DeleteStmt;
617618

618619
/* ----------------------

0 commit comments

Comments
 (0)
0