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

Commit30840c9

Browse files
committed
Allow ALTER VIEW command to rename the column in the view.
ALTER TABLE RENAME COLUMN command always can be used to rename the columnin the view, but it's reasonable to add that syntax to ALTER VIEW too.Author: Fujii MasaoReviewed-by: Ibrar Ahmed, Yu KimuraDiscussion:https://postgr.es/m/CAHGQGwHoQMD3b-MqTLcp1MgdhCpOKU7QNRwjFooT4_d+ti5v6g@mail.gmail.com
1 parent61a956d commit30840c9

File tree

6 files changed

+88
-3
lines changed

6 files changed

+88
-3
lines changed

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

Lines changed: 19 additions & 0 deletions
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

Lines changed: 2 additions & 1 deletion
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

Lines changed: 22 additions & 0 deletions
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+
|ALTERVIEWqualified_nameRENAMEopt_columnnameTOname
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+
|ALTERVIEWIF_PEXISTSqualified_nameRENAMEopt_columnnameTOname
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
|ALTERMATERIALIZEDVIEWqualified_nameRENAMEopt_columnnameTOname
87818803
{
87828804
RenameStmt *n = makeNode(RenameStmt);

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

Lines changed: 13 additions & 1 deletion
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
elseif (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+
elseif (Matches("ALTER","VIEW",MatchAny,"RENAME"))
1804+
COMPLETE_WITH_ATTR(prev2_wd," UNION SELECT 'COLUMN' UNION SELECT 'TO'");
1805+
elseif (Matches("ALTER","VIEW",MatchAny,"ALTER|RENAME","COLUMN"))
1806+
COMPLETE_WITH_ATTR(prev3_wd,"");
1807+
/* ALTER VIEW xxx RENAME yyy */
1808+
elseif (Matches("ALTER","VIEW",MatchAny,"RENAME",MatchAnyExcept("TO")))
1809+
COMPLETE_WITH("TO");
1810+
/* ALTER VIEW xxx RENAME COLUMN yyy */
1811+
elseif (Matches("ALTER","VIEW",MatchAny,"RENAME","COLUMN",MatchAnyExcept("TO")))
1812+
COMPLETE_WITH("TO");
1813+
18021814
/* ALTER MATERIALIZED VIEW <name> */
18031815
elseif (Matches("ALTER","MATERIALIZED","VIEW",MatchAny))
18041816
COMPLETE_WITH("ALTER COLUMN","CLUSTER ON","DEPENDS ON EXTENSION",

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

Lines changed: 26 additions & 1 deletion
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 to63 other objects
1809+
NOTICE: drop cascades to64 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

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -391,6 +391,12 @@ select pg_get_viewdef('vv1', true);
391391
altertable tt5 drop column c;
392392
select pg_get_viewdef('vv1', true);
393393

394+
createviewv4asselect*from v1;
395+
alterview 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
createtablett7 (xint, xxint, yint);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp