Re: Allow CREATE OR REPLACE VIEW to rename the columns

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.

Regards,

--
Fujii Masao