Using views in Postgres can be a painful process. Changing a column in a
base table will require you to drop all views that depend on it, and all
views that depend on those views and so on.
My coworker was complaining this morning that he now has a bunch of
queries where a view is joined back on the original table to add a
column that was missing from the view. It was easier to do this than to
drop the view and all of it's dependencies and then find all the source
code and rebuild all of the views in the correct order.
So my thought was to create an invalid objects table to store the source
and dependencies (and possibly permissions) when a DDL change
invalidates a view or a function. And later you can call a procedure
that (tries to) rebuild those invalid objects.
My initial plan of attack is to just create a function that stores the
information required to rebuild the dependencies before dropping them.
Something like:
store_and_drop('my_view_name')
I'm thinking that ultimately it would be nice if postgres could do this
automatically. Maybe:
DROP my_view_name CASCADE WITH RESTORE
So before I begin, has anyone already done this? And does anyone have
any advice as to how it may best be done?
Thanks
Scott Bailey
