Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf5ab0a1

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_ implicitrange table entries -- in the past, we would only warn about implicitRTEs in SELECTs with at least one explicit RTE.As a result of the warning change, 25 of the regression tests had tobe updated. I also took the opportunity to remove some bogus whitespacedifferences between some of the float4 and float8 variants. I believeI have correctly updated all the platform-specific variants, but letme know if that's not the case.Original patch for DELETE ... USING from Euler Taveira de Oliveira,reworked by Neil Conway.
1 parentbe2f825 commitf5ab0a1

File tree

68 files changed

+393
-234
lines changed

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
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
returnnewnode;
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
* IDENTIFICATION
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
* AUTHORDATEMAJOR EVENT
@@ -229,7 +229,7 @@ static void doNegateFloat(Value *v);
229229
transaction_mode_list_or_empty
230230
TableFuncElementList
231231
prep_type_clauseprep_type_list
232-
execute_param_clause
232+
execute_param_clauseusing_clause
233233

234234
%type<range>into_clauseOptTempTableName
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_exprusing_clausewhere_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
staticvoid
19751974
warnAutoRange(ParseState*pstate,RangeVar*relation)
19761975
{
1977-
boolfoundInFromCl= 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
staticvoidget_agg_expr(Aggref*aggref,deparse_context*context);
200200
staticvoidget_const_expr(Const*constval,deparse_context*context);
201201
staticvoidget_sublink_expr(SubLink*sublink,deparse_context*context);
202-
staticvoidget_from_clause(Query*query,deparse_context*context);
202+
staticvoidget_from_clause(Query*query,constchar*prefix,
203+
deparse_context*context);
203204
staticvoidget_from_clause_item(Node*jtnode,Query*query,
204205
deparse_context*context);
205206
staticvoidget_from_clause_alias(Alias*alias,intvarno,
@@ -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
staticvoid
3811-
get_from_clause(Query*query,deparse_context*context)
3819+
get_from_clause(Query*query,constchar*prefix,deparse_context*context)
38123820
{
38133821
StringInfobuf=context->buf;
38143822
boolfirst= 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
elseif (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
elseif (pg_strcasecmp(prev3_wd,"DELETE")==0&&
11691169
pg_strcasecmp(prev2_wd,"FROM")==0)
1170-
COMPLETE_WITH_CONST("WHERE");
1170+
{
1171+
staticconstchar*constlist_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
NodeTagtype;
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)
[8]ページ先頭
©2009-2025 Movatter.jp