Postgresql – Why is it not possible to alter a view with a “CREATE OR REPLACE”

postgresqlview

The Postgresql documentation is clear on this point: you can not change the columns of an existing view.

The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types) (…)

Reference: http://www.postgresql.org/docs/9.3/static/sql-createview.html

Why on earth do I have to either DROP the view or ALTER it for modifying it ?

Best Answer

It's an implementation limitation. It's theoretically possible, of course, but nobody's written the code to handle it yet.

To cope with column removals or type changes, PostgreSQL would have to scan every view that references the view being modified (using pg_catalog.pg_depend) to see if any of them relied on the column. It'd also need to look for whole-row references and disallow changes in those cases.

It's less clear why adding a column is not permitted. Again, I suspect that's down to whole-row references. If pg_depend was checked for whole-row references without finding any, and the new column appeared at the end, it'd be OK to add it.

However, views created with SELECT * FROM wouldn't "inherit" the new column, because * gets expanded into a column-list during view creation, though. So if you had view_A that does a SELECT * FROM view_B, and you added a column to view_B, it wouldn't appear in view_A. Yet if you dropped and re-created view_A, the column would appear. Needless to say, that's not good. To cope with that, PostgreSQL would have to keep track of whether a given view's column list ("targetlist" in PostgreSQL internal terms) came from a * wildcard. Which is more complicated than you'd think because you can write someview.* too.

All in all - it's complicated, and nobody's wanted it enough to do the work to implement it.