How to ALTER a view in PostgreSQL

Question

Folks,

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 has 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?

Thank you.

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.