aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/ref/delete.sgml17
-rw-r--r--doc/src/sgml/ref/update.sgml20
-rw-r--r--doc/src/sgml/regress.sgml4
-rw-r--r--src/backend/parser/gram.y22
-rw-r--r--src/backend/parser/parse_clause.c4
-rw-r--r--src/test/regress/expected/delete.out27
-rw-r--r--src/test/regress/expected/rowtypes.out3
-rw-r--r--src/test/regress/expected/update.out25
-rw-r--r--src/test/regress/parallel_schedule2
-rw-r--r--src/test/regress/serial_schedule3
-rw-r--r--src/test/regress/sql/delete.sql22
-rw-r--r--src/test/regress/sql/rowtypes.sql3
-rw-r--r--src/test/regress/sql/update.sql16
13 files changed, 155 insertions, 13 deletions
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 8369d991372..b61e6cacd21 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -1,5 +1,5 @@
<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.25 2005/11/01 21:09:50 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.26 2006/01/22 05:20:33 neilc Exp $
PostgreSQL documentation
-->
@@ -20,7 +20,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable>
+DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
[ USING <replaceable class="PARAMETER">usinglist</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
</synopsis>
@@ -92,6 +92,19 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable>
</varlistentry>
<varlistentry>
+ <term><replaceable class="parameter">alias</replaceable></term>
+ <listitem>
+ <para>
+ A substitute name for the target table. When an alias is
+ provided, it completely hides the actual name of the table. For
+ example, given <literal>DELETE FROM foo AS f</>, the remainder
+ of the <command>DELETE</command> statement must refer to this
+ table as <literal>f</> not <literal>foo</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="PARAMETER">usinglist</replaceable></term>
<listitem>
<para>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 503f41de12b..95e4310ab42 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -1,5 +1,5 @@
<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.34 2006/01/19 23:09:42 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.35 2006/01/22 05:20:33 neilc Exp $
PostgreSQL documentation
-->
@@ -20,7 +20,8 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> SET <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...]
+UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
+ SET <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...]
[ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
</synopsis>
@@ -74,6 +75,21 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> SET <replacea
</varlistentry>
<varlistentry>
+ <term><replaceable class="parameter">alias</replaceable></term>
+ <listitem>
+ <para>
+ A substitute name for the target table. When an alias is
+ provided, it completely hides the actual name of the table. For
+ example, given <literal>UPDATE foo AS f</>, the remainder of the
+ <command>UPDATE</command> statement must refer to this table as
+ <literal>f</> not <literal>foo</>. You cannot use the alias in
+ the <literal>SET</literal> clause. For example, <literal>SET
+ f.col = 1</> is invalid.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="PARAMETER">column</replaceable></term>
<listitem>
<para>
diff --git a/doc/src/sgml/regress.sgml b/doc/src/sgml/regress.sgml
index 05749a3157b..11f9468860f 100644
--- a/doc/src/sgml/regress.sgml
+++ b/doc/src/sgml/regress.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/regress.sgml,v 1.49 2005/10/18 21:43:33 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/regress.sgml,v 1.50 2006/01/22 05:20:32 neilc Exp $ -->
<chapter id="regress">
<title id="regress-title">Regression Tests</title>
@@ -49,7 +49,7 @@ gmake check
<screen>
<computeroutput>
======================
- All 98 tests passed.
+ All 100 tests passed.
======================
</computeroutput>
</screen>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 41b22d811c9..22e20165b96 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.522 2006/01/21 02:16:19 momjian Exp $
+ * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.523 2006/01/22 05:20:33 neilc Exp $
*
* HISTORY
* AUTHOR DATE MAJOR EVENT
@@ -291,6 +291,7 @@ static void doNegateFloat(Value *v);
%type <node> table_ref
%type <jexpr> joined_table
%type <range> relation_expr
+%type <range> relation_expr_opt_alias
%type <target> target_el insert_target_el update_target_el insert_column_item
%type <typnam> Typename SimpleTypename ConstTypename
@@ -5148,7 +5149,8 @@ insert_column_item:
*
*****************************************************************************/
-DeleteStmt: DELETE_P FROM relation_expr using_clause where_clause
+DeleteStmt: DELETE_P FROM relation_expr_opt_alias
+ using_clause where_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
n->relation = $3;
@@ -5200,7 +5202,7 @@ opt_nowait: NOWAIT { $$ = TRUE; }
*
*****************************************************************************/
-UpdateStmt: UPDATE relation_expr
+UpdateStmt: UPDATE relation_expr_opt_alias
SET update_target_list
from_clause
where_clause
@@ -5878,6 +5880,20 @@ relation_expr:
;
+relation_expr_opt_alias: relation_expr
+ {
+ $$ = $1;
+ }
+ | relation_expr opt_as IDENT
+ {
+ Alias *alias = makeNode(Alias);
+ alias->aliasname = $3;
+ $1->alias = alias;
+ $$ = $1;
+ }
+ ;
+
+
func_table: func_expr { $$ = $1; }
;
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index aee45f3d205..934802e16ec 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.144 2005/11/22 18:17:16 momjian Exp $
+ * $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.145 2006/01/22 05:20:34 neilc Exp $
*
*-------------------------------------------------------------------------
*/
@@ -160,7 +160,7 @@ setTargetTable(ParseState *pstate, RangeVar *relation,
* Now build an RTE.
*/
rte = addRangeTableEntryForRelation(pstate, pstate->p_target_relation,
- NULL, inh, false);
+ relation->alias, inh, false);
pstate->p_target_rangetblentry = rte;
/* assume new rte is at end */
diff --git a/src/test/regress/expected/delete.out b/src/test/regress/expected/delete.out
new file mode 100644
index 00000000000..68128f69d09
--- /dev/null
+++ b/src/test/regress/expected/delete.out
@@ -0,0 +1,27 @@
+CREATE TABLE delete_test (
+ id SERIAL PRIMARY KEY,
+ a INT
+);
+NOTICE: CREATE TABLE will create implicit sequence "delete_test_id_seq" for serial column "delete_test.id"
+NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "delete_test_pkey" for table "delete_test"
+INSERT INTO delete_test (a) VALUES (10);
+INSERT INTO delete_test (a) VALUES (50);
+INSERT INTO delete_test (a) VALUES (100);
+-- allow an alias to be specified for DELETE's target table
+DELETE FROM delete_test AS dt WHERE dt.a > 75;
+-- if an alias is specified, don't allow the original table name
+-- to be referenced
+BEGIN;
+SET LOCAL add_missing_from = false;
+DELETE FROM delete_test dt WHERE delete_test.a > 25;
+ERROR: invalid reference to FROM-clause entry for table "delete_test"
+HINT: Perhaps you meant to reference the table alias "dt".
+ROLLBACK;
+SELECT * FROM delete_test;
+ id | a
+----+----
+ 1 | 10
+ 2 | 50
+(2 rows)
+
+DROP TABLE delete_test;
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index fc46dd14e19..c6b1bfac398 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -59,8 +59,11 @@ select * from quadtable;
2 | ("(,4.4)","(5.5,6.6)")
(2 rows)
+begin;
+set local add_missing_from = false;
select f1, q.c1 from quadtable; -- fails, q is a table reference
ERROR: missing FROM-clause entry for table "q"
+rollback;
select f1, (q).c1, (qq.q).c1.i from quadtable qq;
f1 | c1 | i
----+-----------+-----
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index 3fca2fb41ac..1ff7c8918f5 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -22,4 +22,29 @@ SELECT * FROM update_test;
10 |
(2 rows)
+-- aliases for the UPDATE target table
+UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
+SELECT * FROM update_test;
+ a | b
+----+----
+ 10 | 10
+ 10 | 10
+(2 rows)
+
+UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
+SELECT * FROM update_test;
+ a | b
+----+----
+ 10 | 20
+ 10 | 20
+(2 rows)
+
+-- if an alias for the target table is specified, don't allow references
+-- to the original table name
+BEGIN;
+SET LOCAL add_missing_from = false;
+UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
+ERROR: invalid reference to FROM-clause entry for table "update_test"
+HINT: Perhaps you meant to reference the table alias "t".
+ROLLBACK;
DROP TABLE update_test;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index d5777794bfb..fef609711b2 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -60,7 +60,7 @@ ignore: random
# ----------
# The fourth group of parallel test
# ----------
-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
+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
test: privileges
test: misc
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index dd60070433c..3342dc2ba1f 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -1,4 +1,4 @@
-# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.29 2005/11/19 17:39:45 adunstan Exp $
+# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.30 2006/01/22 05:20:34 neilc Exp $
# This should probably be in an order similar to parallel_schedule.
test: boolean
test: char
@@ -74,6 +74,7 @@ test: arrays
test: btree_index
test: hash_index
test: update
+test: delete
test: namespace
test: prepared_xacts
test: privileges
diff --git a/src/test/regress/sql/delete.sql b/src/test/regress/sql/delete.sql
new file mode 100644
index 00000000000..86cabfcf648
--- /dev/null
+++ b/src/test/regress/sql/delete.sql
@@ -0,0 +1,22 @@
+CREATE TABLE delete_test (
+ id SERIAL PRIMARY KEY,
+ a INT
+);
+
+INSERT INTO delete_test (a) VALUES (10);
+INSERT INTO delete_test (a) VALUES (50);
+INSERT INTO delete_test (a) VALUES (100);
+
+-- allow an alias to be specified for DELETE's target table
+DELETE FROM delete_test AS dt WHERE dt.a > 75;
+
+-- if an alias is specified, don't allow the original table name
+-- to be referenced
+BEGIN;
+SET LOCAL add_missing_from = false;
+DELETE FROM delete_test dt WHERE delete_test.a > 25;
+ROLLBACK;
+
+SELECT * FROM delete_test;
+
+DROP TABLE delete_test; \ No newline at end of file
diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql
index 613c4e91f91..43d57bc6035 100644
--- a/src/test/regress/sql/rowtypes.sql
+++ b/src/test/regress/sql/rowtypes.sql
@@ -35,7 +35,10 @@ insert into quadtable values (2, ((null,4.4),(5.5,6.6)));
select * from quadtable;
+begin;
+set local add_missing_from = false;
select f1, q.c1 from quadtable; -- fails, q is a table reference
+rollback;
select f1, (q).c1, (qq.q).c1.i from quadtable qq;
diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql
index 577596abb1b..99fd74bca79 100644
--- a/src/test/regress/sql/update.sql
+++ b/src/test/regress/sql/update.sql
@@ -16,4 +16,20 @@ UPDATE update_test SET a = DEFAULT, b = DEFAULT;
SELECT * FROM update_test;
+-- aliases for the UPDATE target table
+UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
+
+SELECT * FROM update_test;
+
+UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
+
+SELECT * FROM update_test;
+
+-- if an alias for the target table is specified, don't allow references
+-- to the original table name
+BEGIN;
+SET LOCAL add_missing_from = false;
+UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
+ROLLBACK;
+
DROP TABLE update_test;
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-02 13:46:12 +0000