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
