2019-10-31 21:01 に Fujii Masao さんは書きました:
On Thu, Oct 31, 2019 at 7:59 PM Ibrar Ahmed <ibrar.ah...@gmail.com>
wrote:
On Thu, Oct 31, 2019 at 12:32 PM Fujii Masao <masao.fu...@gmail.com>
wrote:
On Thu, Oct 31, 2019 at 1:42 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> Fujii Masao <masao.fu...@gmail.com> writes:
> > Currently CREATE OR REPLACE VIEW command fails if the column names
> > are changed.
>
> That is, I believe, intentional. It's an effective aid to catching
> mistakes in view redefinitions, such as misaligning the new set of
> columns relative to the old. That's particularly important given
> that we allow you to add columns during CREATE OR REPLACE VIEW.
> Consider the oversimplified case where you start with
>
> CREATE VIEW v AS SELECT 1 AS x, 2 AS y;
>
> and you want to add a column z, and you get sloppy and write
>
> CREATE OR REPLACE VIEW v AS SELECT 1 AS x, 3 AS z, 2 AS y;
>
> If we did not throw an error on this, references that formerly
> pointed to column y would now point to z (as that's still attnum 2),
> which is highly unlikely to be what you wanted.
This example makes me wonder if the addtion of column by
CREATE OR REPLACE VIEW also has the same (or even worse) issue.
That is, it may increase the oppotunity for users' mistake.
I'm thinking the case where users mistakenly added new column
into the view when replacing the view definition. This mistake can
happen because CREATE OR REPLACE VIEW allows new column to
be added. But what's the worse is that, currently there is no way to
drop the column from the view, except recreation of the view.
Neither CREATE OR REPLACE VIEW nor ALTER TABLE support
the drop of the column from the view. So, to fix the mistake,
users would need to drop the view itself and recreate it. If there
are
some objects depending the view, they also might need to be
recreated.
This looks not good. Since the feature has been supported,
it's too late to say that, though...
At least, the support for ALTER VIEW DROP COLUMN might be
necessary to alleviate that situation.
- Is this intentional not implemented the "RENAME COLUMN" statement
for
VIEW because it is implemented for Materialized View?
Not sure that, but Tom's suggestion to support ALTER VIEW RENAME COLUMN
sounds reasonable whether we support the rename of columns when
replacing
the view definition, or not. Attached is the patch that adds support
for
ALTER VIEW RENAME COLUMN command.
I have made just a similar
change to view and it works.
Yeah, ISTM that we made the same patch at the same time. You changed
gram.y,
but I added the following changes additionally.
- Update the doc
- Add HINT message emit when CRAETE OR REPLACE VIEW fails to rename the
columns
- Update tab-complete.c
I review your patch, and then I found that tab complete of "alter
materialized view" is also not enough.
So, I made a small patch referencing your patch.
Regards,
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 2b1e3cd..c493b3e 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1801,9 +1801,19 @@ psql_completion(const char *text, int start, int end)
"SET SCHEMA");
/* ALTER MATERIALIZED VIEW <name> */
else if (Matches("ALTER", "MATERIALIZED", "VIEW", MatchAny))
- COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME TO",
+ COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME",
"SET SCHEMA");
-
+ /* ALTER MATERIALIZED VIEW xxx RENAME */
+ else if (Matches("ALTER", "MATERIALIZED", "VIEW", MatchAny, "RENAME"))
+ COMPLETE_WITH_ATTR(prev2_wd, " UNION SELECT 'COLUMN' UNION SELECT 'TO'");
+ else if (Matches("ALTER", "MATERIALIZED", "VIEW", MatchAny, "ALTER|RENAME", "COLUMN"))
+ COMPLETE_WITH_ATTR(prev3_wd, "");
+ /* ALTER MATERIALIZED VIEW xxx RENAME yyy */
+ else if (Matches("ALTER", "MATERIALIZED", "VIEW", MatchAny, "RENAME", MatchAnyExcept("TO")))
+ COMPLETE_WITH("TO");
+ /* ALTER MATERIALIZED VIEW xxx RENAME COLUMN yyy */
+ else if (Matches("ALTER", "MATERIALIZED", "VIEW", MatchAny, "RENAME", "COLUMN", MatchAnyExcept("TO")))
+ COMPLETE_WITH("TO");
/* ALTER POLICY <name> */
else if (Matches("ALTER", "POLICY"))
COMPLETE_WITH_QUERY(Query_for_list_of_policies);
