Skip to content

Commit 1d763d9

Browse files
author
Neil Conway
committed
Allow an optional alias for the target table to be specified for UPDATE
and DELETE. If specified, the alias must be used instead of the full table name. Also, the alias currently cannot be used in the SET clause of UPDATE. Patch from Atsushi Ogawa, various editorialization by Neil Conway. Along the way, make the rowtypes regression test pass if add_missing_from is enabled, and add a new (skeletal) regression test for DELETE.
1 parent 57a84ca commit 1d763d9

File tree

13 files changed

+155
-13
lines changed

13 files changed

+155
-13
lines changed

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

Lines changed: 15 additions & 2 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.25 2005/11/01 21:09:50 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.26 2006/01/22 05:20:33 neilc Exp $
33
PostgreSQL documentation
44
-->
55

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

2121
<refsynopsisdiv>
2222
<synopsis>
23-
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable>
23+
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
2424
[ USING <replaceable class="PARAMETER">usinglist</replaceable> ]
2525
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
2626
</synopsis>
@@ -91,6 +91,19 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable>
9191
</listitem>
9292
</varlistentry>
9393

94+
<varlistentry>
95+
<term><replaceable class="parameter">alias</replaceable></term>
96+
<listitem>
97+
<para>
98+
A substitute name for the target table. When an alias is
99+
provided, it completely hides the actual name of the table. For
100+
example, given <literal>DELETE FROM foo AS f</>, the remainder
101+
of the <command>DELETE</command> statement must refer to this
102+
table as <literal>f</> not <literal>foo</>.
103+
</para>
104+
</listitem>
105+
</varlistentry>
106+
94107
<varlistentry>
95108
<term><replaceable class="PARAMETER">usinglist</replaceable></term>
96109
<listitem>

‎doc/src/sgml/ref/update.sgml

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.34 2006/01/19 23:09:42 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.35 2006/01/22 05:20:33 neilc Exp $
33
PostgreSQL documentation
44
-->
55

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

2121
<refsynopsisdiv>
2222
<synopsis>
23-
UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> SET <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...]
23+
UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
24+
SET <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...]
2425
[ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
2526
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
2627
</synopsis>
@@ -73,6 +74,21 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> SET <replacea
7374
</listitem>
7475
</varlistentry>
7576

77+
<varlistentry>
78+
<term><replaceable class="parameter">alias</replaceable></term>
79+
<listitem>
80+
<para>
81+
A substitute name for the target table. When an alias is
82+
provided, it completely hides the actual name of the table. For
83+
example, given <literal>UPDATE foo AS f</>, the remainder of the
84+
<command>UPDATE</command> statement must refer to this table as
85+
<literal>f</> not <literal>foo</>. You cannot use the alias in
86+
the <literal>SET</literal> clause. For example, <literal>SET
87+
f.col = 1</> is invalid.
88+
</para>
89+
</listitem>
90+
</varlistentry>
91+
7692
<varlistentry>
7793
<term><replaceable class="PARAMETER">column</replaceable></term>
7894
<listitem>

‎doc/src/sgml/regress.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/regress.sgml,v 1.49 2005/10/18 21:43:33 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/regress.sgml,v 1.50 2006/01/22 05:20:32 neilc Exp $ -->
22

33
<chapter id="regress">
44
<title id="regress-title">Regression Tests</title>
@@ -49,7 +49,7 @@ gmake check
4949
<screen>
5050
<computeroutput>
5151
======================
52-
All 98 tests passed.
52+
All 100 tests passed.
5353
======================
5454
</computeroutput>
5555
</screen>

‎src/backend/parser/gram.y

Lines changed: 19 additions & 3 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.522 2006/01/21 02:16:19 momjian Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.523 2006/01/22 05:20:33 neilc Exp $
1515
*
1616
* HISTORY
1717
* AUTHOR DATE MAJOR EVENT
@@ -291,6 +291,7 @@ static void doNegateFloat(Value *v);
291291
%type <node> table_ref
292292
%type <jexpr> joined_table
293293
%type <range> relation_expr
294+
%type <range> relation_expr_opt_alias
294295
%type <target> target_el insert_target_el update_target_el insert_column_item
295296

296297
%type <typnam> Typename SimpleTypename ConstTypename
@@ -5148,7 +5149,8 @@ insert_column_item:
51485149
*
51495150
*****************************************************************************/
51505151

5151-
DeleteStmt: DELETE_P FROM relation_expr using_clause where_clause
5152+
DeleteStmt: DELETE_P FROM relation_expr_opt_alias
5153+
using_clause where_clause
51525154
{
51535155
DeleteStmt *n = makeNode(DeleteStmt);
51545156
n->relation = $3;
@@ -5200,7 +5202,7 @@ opt_nowait: NOWAIT { $$ = TRUE; }
52005202
*
52015203
*****************************************************************************/
52025204

5203-
UpdateStmt: UPDATE relation_expr
5205+
UpdateStmt: UPDATE relation_expr_opt_alias
52045206
SET update_target_list
52055207
from_clause
52065208
where_clause
@@ -5878,6 +5880,20 @@ relation_expr:
58785880
;
58795881

58805882

5883+
relation_expr_opt_alias: relation_expr
5884+
{
5885+
$$ = $1;
5886+
}
5887+
| relation_expr opt_as IDENT
5888+
{
5889+
Alias *alias = makeNode(Alias);
5890+
alias->aliasname = $3;
5891+
$1->alias = alias;
5892+
$$ = $1;
5893+
}
5894+
;
5895+
5896+
58815897
func_table: func_expr { $$ = $1; }
58825898
;
58835899

‎src/backend/parser/parse_clause.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.144 2005/11/22 18:17:16 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.145 2006/01/22 05:20:34 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -160,7 +160,7 @@ setTargetTable(ParseState *pstate, RangeVar *relation,
160160
* Now build an RTE.
161161
*/
162162
rte = addRangeTableEntryForRelation(pstate, pstate->p_target_relation,
163-
NULL, inh, false);
163+
relation->alias, inh, false);
164164
pstate->p_target_rangetblentry = rte;
165165

166166
/* assume new rte is at end */

‎src/test/regress/expected/delete.out

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,27 @@
1+
CREATE TABLE delete_test (
2+
id SERIAL PRIMARY KEY,
3+
a INT
4+
);
5+
NOTICE: CREATE TABLE will create implicit sequence "delete_test_id_seq" for serial column "delete_test.id"
6+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "delete_test_pkey" for table "delete_test"
7+
INSERT INTO delete_test (a) VALUES (10);
8+
INSERT INTO delete_test (a) VALUES (50);
9+
INSERT INTO delete_test (a) VALUES (100);
10+
-- allow an alias to be specified for DELETE's target table
11+
DELETE FROM delete_test AS dt WHERE dt.a > 75;
12+
-- if an alias is specified, don't allow the original table name
13+
-- to be referenced
14+
BEGIN;
15+
SET LOCAL add_missing_from = false;
16+
DELETE FROM delete_test dt WHERE delete_test.a > 25;
17+
ERROR: invalid reference to FROM-clause entry for table "delete_test"
18+
HINT: Perhaps you meant to reference the table alias "dt".
19+
ROLLBACK;
20+
SELECT * FROM delete_test;
21+
id | a
22+
----+----
23+
1 | 10
24+
2 | 50
25+
(2 rows)
26+
27+
DROP TABLE delete_test;

‎src/test/regress/expected/rowtypes.out

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -59,8 +59,11 @@ select * from quadtable;
5959
2 | ("(,4.4)","(5.5,6.6)")
6060
(2 rows)
6161

62+
begin;
63+
set local add_missing_from = false;
6264
select f1, q.c1 from quadtable; -- fails, q is a table reference
6365
ERROR: missing FROM-clause entry for table "q"
66+
rollback;
6467
select f1, (q).c1, (qq.q).c1.i from quadtable qq;
6568
f1 | c1 | i
6669
----+-----------+-----

‎src/test/regress/expected/update.out

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,4 +22,29 @@ SELECT * FROM update_test;
2222
10 |
2323
(2 rows)
2424

25+
-- aliases for the UPDATE target table
26+
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
27+
SELECT * FROM update_test;
28+
a | b
29+
----+----
30+
10 | 10
31+
10 | 10
32+
(2 rows)
33+
34+
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
35+
SELECT * FROM update_test;
36+
a | b
37+
----+----
38+
10 | 20
39+
10 | 20
40+
(2 rows)
41+
42+
-- if an alias for the target table is specified, don't allow references
43+
-- to the original table name
44+
BEGIN;
45+
SET LOCAL add_missing_from = false;
46+
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
47+
ERROR: invalid reference to FROM-clause entry for table "update_test"
48+
HINT: Perhaps you meant to reference the table alias "t".
49+
ROLLBACK;
2550
DROP TABLE update_test;

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -60,7 +60,7 @@ ignore: random
6060
# ----------
6161
# The fourth group of parallel test
6262
# ----------
63-
test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts
63+
test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
6464

6565
test: privileges
6666
test: misc

‎src/test/regress/serial_schedule

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.29 2005/11/19 17:39:45 adunstan Exp $
1+
# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.30 2006/01/22 05:20:34 neilc Exp $
22
# This should probably be in an order similar to parallel_schedule.
33
test: boolean
44
test: char
@@ -74,6 +74,7 @@ test: arrays
7474
test: btree_index
7575
test: hash_index
7676
test: update
77+
test: delete
7778
test: namespace
7879
test: prepared_xacts
7980
test: privileges

‎src/test/regress/sql/delete.sql

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
CREATE TABLE delete_test (
2+
id SERIAL PRIMARY KEY,
3+
a INT
4+
);
5+
6+
INSERT INTO delete_test (a) VALUES (10);
7+
INSERT INTO delete_test (a) VALUES (50);
8+
INSERT INTO delete_test (a) VALUES (100);
9+
10+
-- allow an alias to be specified for DELETE's target table
11+
DELETE FROM delete_test AS dt WHERE dt.a > 75;
12+
13+
-- if an alias is specified, don't allow the original table name
14+
-- to be referenced
15+
BEGIN;
16+
SET LOCAL add_missing_from = false;
17+
DELETE FROM delete_test dt WHERE delete_test.a > 25;
18+
ROLLBACK;
19+
20+
SELECT * FROM delete_test;
21+
22+
DROP TABLE delete_test;

‎src/test/regress/sql/rowtypes.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,7 +35,10 @@ insert into quadtable values (2, ((null,4.4),(5.5,6.6)));
3535

3636
select * from quadtable;
3737

38+
begin;
39+
set local add_missing_from = false;
3840
select f1, q.c1 from quadtable; -- fails, q is a table reference
41+
rollback;
3942

4043
select f1, (q).c1, (qq.q).c1.i from quadtable qq;
4144

‎src/test/regress/sql/update.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,4 +16,20 @@ UPDATE update_test SET a = DEFAULT, b = DEFAULT;
1616

1717
SELECT * FROM update_test;
1818

19+
-- aliases for the UPDATE target table
20+
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
21+
22+
SELECT * FROM update_test;
23+
24+
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
25+
26+
SELECT * FROM update_test;
27+
28+
-- if an alias for the target table is specified, don't allow references
29+
-- to the original table name
30+
BEGIN;
31+
SET LOCAL add_missing_from = false;
32+
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
33+
ROLLBACK;
34+
1935
DROP TABLE update_test;

0 commit comments

Comments
 (0)