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? 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
001_alter_view_rename_column_ibrar_v1.patch
Description: Binary data
