Tuesday, July 29, 2008

[HACKERS] modifying views

I've been thinking a little more about modifying views:

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00694.php

AFAICS, out of all of the things that ALTER TABLE is capable of doing,
there are only four that make any sense in the context of views:

(1) add a column
(2) drop a column
(3) change the type of an existing column
(4) renaming a column

The tricky part of course is that the underlying query has to change
along with the type definition. It's fairly obvious what the
semantics of dropping a column should be, but the other cases are
trickier. New columns could possibly be returned as NULL (pending a
subsequent CREATE OR REPLACE VIEW), but changing the type of a column
just seems crazy as a standalone operation. The bigger problem though
is that I'm pretty sure that ALTER TABLE <viewname> ADD COLUMN <name>
<type> is not a command that anyone is eager to be typing. What we
really want to be able to do is a CREATE OR REPLACE VIEW command and
have the system worry about what columns needed to be added, dropped,
or retyped, and giving us an error if we've tried to (for example)
remove a column that some other view depends on. (Apart from CREATE
OR REPLACE VIEW, the only operation that looks independently useful to
me is RENAME COLUMN.)

Unfortunately, it looks to me like a fully general implementation of
this feature would be Really Hard, because a CREATE OR REPLACE VIEW
command, beyond attempting to add, drop, or retype columns, could also
attempt to reorder them. A cursory inspection of the situation
suggests this would require modifying the attnum values in
pg_attribute, which looks like a nightmare. (Anyone want to argue
otherwise? Tom? I'd love to be wrong about this...) However, it
might be possible to allow some or all of the following: (1) dropping
columns from anywhere, (2) adding new columns at the end, and (3)
changing the type of existing columns. (I think (1) and (2) are
clearly more useful than (3).)

Thoughts?

...Robert

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

No comments: