Skip to content

Commit 30840c9

Browse files
MasaoFujiiMasaoFujii
committed
Allow ALTER VIEW command to rename the column in the view.
ALTER TABLE RENAME COLUMN command always can be used to rename the column in the view, but it's reasonable to add that syntax to ALTER VIEW too. Author: Fujii Masao Reviewed-by: Ibrar Ahmed, Yu Kimura Discussion: https://postgr.es/m/CAHGQGwHoQMD3b-MqTLcp1MgdhCpOKU7QNRwjFooT4_d+ti5v6g@mail.gmail.com
1 parent 61a956d commit 30840c9

File tree

6 files changed

+88
-3
lines changed

6 files changed

+88
-3
lines changed

‎doc/src/sgml/ref/alter_view.sgml

+19
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@ PostgreSQL documentation
2424
ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable>
2525
ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT
2626
ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_USER | SESSION_USER }
27+
ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME [ COLUMN ] <replaceable class="parameter">column_name</replaceable> TO <replaceable class="parameter">new_column_name</replaceable>
2728
ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
2829
ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
2930
ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] )
@@ -65,6 +66,24 @@ ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET
6566
</listitem>
6667
</varlistentry>
6768

69+
<varlistentry>
70+
<term><replaceable class="parameter">column_name</replaceable></term>
71+
<listitem>
72+
<para>
73+
Name of an existing column.
74+
</para>
75+
</listitem>
76+
</varlistentry>
77+
78+
<varlistentry>
79+
<term><replaceable class="parameter">new_column_name</replaceable></term>
80+
<listitem>
81+
<para>
82+
New name for an existing column.
83+
</para>
84+
</listitem>
85+
</varlistentry>
86+
6887
<varlistentry>
6988
<term><literal>IF EXISTS</literal></term>
7089
<listitem>

‎src/backend/commands/view.c

+2-1
Original file line numberDiff line numberDiff line change
@@ -276,7 +276,8 @@ checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc)
276276
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
277277
errmsg("cannot change name of view column \"%s\" to \"%s\"",
278278
NameStr(oldattr->attname),
279-
NameStr(newattr->attname))));
279+
NameStr(newattr->attname)),
280+
errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead.")));
280281
/* XXX would it be safe to allow atttypmod to change? Not sure */
281282
if (newattr->atttypid != oldattr->atttypid ||
282283
newattr->atttypmod != oldattr->atttypmod)

‎src/backend/parser/gram.y

+22
Original file line numberDiff line numberDiff line change
@@ -8777,6 +8777,28 @@ RenameStmt: ALTER AGGREGATE aggregate_with_argtypes RENAME TO name
87778777
n->missing_ok = true;
87788778
$$ = (Node *)n;
87798779
}
8780+
| ALTER VIEW qualified_name RENAME opt_column name TO name
8781+
{
8782+
RenameStmt *n = makeNode(RenameStmt);
8783+
n->renameType = OBJECT_COLUMN;
8784+
n->relationType = OBJECT_VIEW;
8785+
n->relation = $3;
8786+
n->subname = $6;
8787+
n->newname = $8;
8788+
n->missing_ok = false;
8789+
$$ = (Node *)n;
8790+
}
8791+
| ALTER VIEW IF_P EXISTS qualified_name RENAME opt_column name TO name
8792+
{
8793+
RenameStmt *n = makeNode(RenameStmt);
8794+
n->renameType = OBJECT_COLUMN;
8795+
n->relationType = OBJECT_VIEW;
8796+
n->relation = $5;
8797+
n->subname = $8;
8798+
n->newname = $10;
8799+
n->missing_ok = true;
8800+
$$ = (Node *)n;
8801+
}
87808802
| ALTER MATERIALIZED VIEW qualified_name RENAME opt_column name TO name
87818803
{
87828804
RenameStmt *n = makeNode(RenameStmt);

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

+13-1
Original file line numberDiff line numberDiff line change
@@ -1797,8 +1797,20 @@ psql_completion(const char *text, int start, int end)
17971797
COMPLETE_WITH("TO");
17981798
/* ALTER VIEW <name> */
17991799
else if (Matches("ALTER", "VIEW", MatchAny))
1800-
COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME TO",
1800+
COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME",
18011801
"SET SCHEMA");
1802+
/* ALTER VIEW xxx RENAME */
1803+
else if (Matches("ALTER", "VIEW", MatchAny, "RENAME"))
1804+
COMPLETE_WITH_ATTR(prev2_wd, " UNION SELECT 'COLUMN' UNION SELECT 'TO'");
1805+
else if (Matches("ALTER", "VIEW", MatchAny, "ALTER|RENAME", "COLUMN"))
1806+
COMPLETE_WITH_ATTR(prev3_wd, "");
1807+
/* ALTER VIEW xxx RENAME yyy */
1808+
else if (Matches("ALTER", "VIEW", MatchAny, "RENAME", MatchAnyExcept("TO")))
1809+
COMPLETE_WITH("TO");
1810+
/* ALTER VIEW xxx RENAME COLUMN yyy */
1811+
else if (Matches("ALTER", "VIEW", MatchAny, "RENAME", "COLUMN", MatchAnyExcept("TO")))
1812+
COMPLETE_WITH("TO");
1813+
18021814
/* ALTER MATERIALIZED VIEW <name> */
18031815
else if (Matches("ALTER", "MATERIALIZED", "VIEW", MatchAny))
18041816
COMPLETE_WITH("ALTER COLUMN", "CLUSTER ON", "DEPENDS ON EXTENSION",

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

+26-1
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,7 @@ ERROR: cannot drop columns from view
6464
CREATE OR REPLACE VIEW viewtest AS
6565
SELECT 1, * FROM viewtest_tbl;
6666
ERROR: cannot change name of view column "a" to "?column?"
67+
HINT: Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead.
6768
-- should fail
6869
CREATE OR REPLACE VIEW viewtest AS
6970
SELECT a, b::numeric FROM viewtest_tbl;
@@ -1189,6 +1190,29 @@ select pg_get_viewdef('vv1', true);
11891190
CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
11901191
(1 row)
11911192

1193+
create view v4 as select * from v1;
1194+
alter view v1 rename column a to x;
1195+
select pg_get_viewdef('v1', true);
1196+
pg_get_viewdef
1197+
---------------------------------------------------
1198+
SELECT tt2.b, +
1199+
tt3.c, +
1200+
tt2.a AS x, +
1201+
tt3.ax +
1202+
FROM tt2 +
1203+
JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
1204+
(1 row)
1205+
1206+
select pg_get_viewdef('v4', true);
1207+
pg_get_viewdef
1208+
----------------
1209+
SELECT v1.b, +
1210+
v1.c, +
1211+
v1.x AS a,+
1212+
v1.ax +
1213+
FROM v1;
1214+
(1 row)
1215+
11921216
-- Unnamed FULL JOIN USING is lots of fun too
11931217
create table tt7 (x int, xx int, y int);
11941218
alter table tt7 drop column xx;
@@ -1782,7 +1806,7 @@ drop cascades to view aliased_view_2
17821806
drop cascades to view aliased_view_3
17831807
drop cascades to view aliased_view_4
17841808
DROP SCHEMA testviewschm2 CASCADE;
1785-
NOTICE: drop cascades to 63 other objects
1809+
NOTICE: drop cascades to 64 other objects
17861810
DETAIL: drop cascades to table t1
17871811
drop cascades to view temporal1
17881812
drop cascades to view temporal2
@@ -1818,6 +1842,7 @@ drop cascades to view v3
18181842
drop cascades to table tt5
18191843
drop cascades to table tt6
18201844
drop cascades to view vv1
1845+
drop cascades to view v4
18211846
drop cascades to table tt7
18221847
drop cascades to table tt8
18231848
drop cascades to view vv2

‎src/test/regress/sql/create_view.sql

+6
Original file line numberDiff line numberDiff line change
@@ -391,6 +391,12 @@ select pg_get_viewdef('vv1', true);
391391
alter table tt5 drop column c;
392392
select pg_get_viewdef('vv1', true);
393393

394+
create view v4 as select * from v1;
395+
alter view v1 rename column a to x;
396+
select pg_get_viewdef('v1', true);
397+
select pg_get_viewdef('v4', true);
398+
399+
394400
-- Unnamed FULL JOIN USING is lots of fun too
395401

396402
create table tt7 (x int, xx int, y int);

0 commit comments

Comments
 (0)