Обсуждение: ERROR: cannot change name of view column

=?UTF-8?Q?R=C3=A9al_A=2E_Carbonneau?= <contact@realcarbonneau.com> writes:
> Changing the name of any column in a view gives the error "ERROR: cannot
> change name of view column".

In CREATE OR REPLACE VIEW, you mean?  Yeah, that's intentional.

> Thus, the original error message is not completely correct.  Ideally,
> the CREATE OR REPLACE VIEW would automatically apply the ALTER TABLE ...
> RENAME COLUMN (or underlying procedure) since it is available and fulfills
> the original DDL request, thus making the error message unnecessary.

DWIM doesn't seem like a good idea here to me.  Doing what you suggest
would be far more likely to let mistakes get by undetected than to
actually do what the user intended.  In many cases, the column types
are all alike, so that the column names are the only cross-check there
is that the new view definition matches up with the old.

For example, suppose you originally wrote

CREATE VIEW v AS SELECT x, y FROM ...

and then you want to change something in the FROM clause, but you
fat-finger the replacement command as

CREATE OR REPLACE VIEW v AS SELECT x, z FROM ...

If y and z are of the same datatype, this would pass undetected
if we try to be "helpful" in this way.

Another class of examples involves accidentally switching the order of
view output columns.  This would interact especially badly with the
fact that we do allow CREATE OR REPLACE VIEW to append columns.
If you should have written

CREATE OR REPLACE VIEW v AS SELECT x, y, z FROM ...

to add "z" to the original list, but get the order wrong, this proposal
would allow very serious breakage of existing view users to occur.
(Previously-stored views will refer to those columns by number, not name,
which is really necessary to allow ALTER VIEW RENAME COLUMN to work at
all.)

> Or at the very least, the error message could be changed to suggest using
> the ALTER TABLE ... RENAME COLUMN until the feature is included in the
> CREATE OR REPLACE VIEW.

Perhaps there's room for a "HINT: if you intended to change view
column names, use ALTER VIEW RENAME COLUMN".  But I suspect such
a hint would get printed in many cases where it didn't apply,
making it perhaps more confusing not less.

            regards, tom lane

Since CREATE ... OR REPLACE  is literally: "if exists, drop and create, else, create",

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, January 1, 2019, Réal A. Carbonneau <contact@realcarbonneau.com>
> wrote:
>> Since CREATE ... OR REPLACE  is literally: "if exists, drop and create,
>> else, create",

> It’s specifically saying “replace the query used for the view without
> changing the view signature/type”.

Right, it's *not* a DROP.  If it were, we'd make dependent views go away,
and the concern for preserving the view's rowtype would be much less.

> Same goes for create or replace
> function.  The point being the signature is public and cannot be changed on
> the fly but the implementation can be replaced.

The analogy to functions is on-point here.  We used to allow CREATE OR
REPLACE FUNCTION to change the names of function parameters, but we had to
disallow that (or at least greatly restrict it, I don't remember details
right now) when we started allowing calls that use parameter names to
disambiguate arguments.

            regards, tom lane