How to ALTER a view in PostgreSQL

Question

PostgreSQL does not allow altering a view (i.e. adding column, changing column orders, adding criterie etc.) if it has dependent objects. This is really getting annoying since you have to write a script to:

1. Drop all the dependent objects,
2. Alter the view,
3. Recreate all the dependent objects back again.

I understand that postgreSQL developers have very reasonable concerns to prevent altering views. But do you guys have any scripts/shot-cuts to do all those manual stuff in a single run?

Answer 1

Adding new columns isn't a problem, changing datatypes or changing the order of the columns, that's where you get problems.

1. Don't change the order, it's not that important anyway, just change your query:

   SELECT a, b FROM view_name;

   SELECT b, a FROM view_name;

2. When you have to change a datatype of a column, you have to check the depend objects as well. These might have problems with this new datatype. Just get the definition of this object and recreate after the changes. The information_schema and pg_catalog help you out.

3. Make all changes within a single transaction.

Answer 2

If I place a addtional "drop view xyz; commit;" before the "create or replace view xyz as ..." statement, at least in many cases I resolve the blocking problem described above.