Re: Allow CREATE OR REPLACE VIEW to rename the columns

On Thu, Oct 31, 2019 at 12:32 PM Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> On Thu, Oct 31, 2019 at 1:42 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Fujii Masao <masao(dot)fujii(at)gmail(dot)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? I have made just a
similar
change to view and it works.

ALTER VIEW v RENAME COLUMN d to e;

- For "DROP COLUMN" for VIEW is throwing error.

postgres=# alter view v drop column e;
ERROR: "v" is not a table, composite type, or foreign table

Regards,
>
> --
> Fujii Masao
>
>
>

--
Ibrar Ahmed