Re: Allow CREATE OR REPLACE VIEW to rename the columns

On Thu, Oct 31, 2019 at 10:54 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
> > 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. [example]
>
> > 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.
>
> The idea in CREATE OR REPLACE VIEW is to allow addition of new
> columns at the end, the same as you can do with tables. Checking
> the column name matchups is a way to ensure that you actually do
> add at the end, rather than insert, which wouldn't act as you
> expect. Admittedly it's only heuristic.
>
> We could, perhaps, have insisted that adding a column also requires
> special syntax, but we didn't. Consider for example a case where
> the new column needs to come from an additionally joined table;
> then you have to be able to edit the underlying view definition along
> with adding the column. So that seems like kind of a pain in the
> neck to insist on.
>
> > But what's the worse is that, currently there is no way to
> > drop the column from the view, except recreation of the view.
>
> I think this has been discussed, as well. It's not necessarily
> simple to drop a view output column. For example, if the view
> uses SELECT DISTINCT, removing an output column would have
> semantic effects on the set of rows that can be returned, since
> distinct-ness would now mean something else than it did before.
>
> It's conceivable that we could enumerate all such effects and
> then allow DROP COLUMN (probably replacing the output column
> with a null constant) if none of them apply, but I can't get
> terribly excited about it. The field demand for such a feature
> has not been high. I'd be a bit worried about bugs arising
> from failures to check attisdropped for views, too; so that
> the cost of getting this working might be greater than it seems
> on the surface.

Thanks for the explanation! Understood.

Regards,

--
Fujii Masao