Re: Allow CREATE OR REPLACE VIEW to rename the columns - Mailing list pgsql-hackers

2019-10-31 21:01 に Fujii Masao さんは書きました:
> On Thu, Oct 31, 2019 at 7:59 PM Ibrar Ahmed <ibrar.ahmad@gmail.com> 
> wrote:
>> 
>> 
>> 
>> On Thu, Oct 31, 2019 at 12:32 PM Fujii Masao <masao.fujii@gmail.com> 
>> wrote:
>>> 
>>> On Thu, Oct 31, 2019 at 1:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> >
>>> > Fujii Masao <masao.fujii@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,