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. Regards, -- Fujii Masao
