Re: patch: Add columns via CREATE OR REPLACE VIEW

> But it seems hard to tell the difference between a "rename" and a
> "drop". I think that we aren't going to get far on this until we
> decide what we will consider to be the identity of a view column.
> With regular tables the attnum is a persistent identifier, but that
> doesn't seem to play nicely for REPLACE VIEW, at least not if you're
> wanting to allow people to remove columns from their view definitions.
>
> Maybe the right way is to *not* use CREATE OR REPLACE VIEW, but
> rather ALTER VIEW ADD COLUMN and so on. Then column identity seems
> a lot easier to keep track of.
>
> Thoughts?

ISTM that column identity should logically follow column name. If a
user types "CREATE OR REPLACE VIEW sandwhich (bacon, lettuce, bread)
..." and sometime later types "CREATE OR REPLACE VIEW sandwich (bacon,
lettuce, tomato, bread) ..." it seems overwhelmingly likely that their
intention was to insert tomato between lettuce and bread rather than
to rename bread to tomato and add a new column that happens to also be
called bread. If they want the other interpretation, they should use
do "ALTER VIEW sandwhich RENAME COLUMN tomato TO bread" before
executing "CREATE OR REPLACE VIEW".

The problem with "ALTER VIEW ADD COLUMN" is that the new column won't
be of any use until someone does "CREATE OR REPLACE VIEW" to update
the underlying query. And if they're already doing "CREATE OR REPLACE
VIEW", then we might as well let "CREATE OR REPLACE VIEW" generate the
column definitions automatically rather than forcing them to do the
same thing by hand (just as we allowed the user to create the view in
the first place without insisting on explicit column definitions).
The problem is even worse for "ALTER VIEW ALTER COLUMN TYPE". What
exactly will the semantics of the view be after this operation but
before a subsequent update of the query via "CREATE OR REPLACE VIEW"?
There are various options but none of them make much sense.

If you accept the idea that column identity should be based on column
name, then the only two operations that are really necessary are
"CREATE OR REPLACE VIEW" and "ALTER VIEW RENAME COLUMN", and it is
100% clear what the semantics of those operations should be. We may
not choose to fully support all possible alterations that can be made
through this framework for some time to come, but it's extremely easy
to understand where we're trying to get to. If you want to
additionally have "ALTER VIEW ADD/DROP COLUMN" available for those
that may wish to use them, presumably returning NULL for any column
that isn't generated by the query, that's also easy to understand and
well-defined.

...Robert