Fujii Masao <masao.fujii@gmail.com> writes:
> 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. [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.
regards, tom lane
