Re: manage changes to views having depencies

On Wed, Dec 31, 2008 at 9:57 PM, Eric Worden <worden(dot)eric(at)gmail(dot)com> wrote:
> Can anyone recommend a reasonably efficient system for changing a view
> definition (say by adding a column) when it has a bunch of dependent
> functions?
>
> Right now I work with the output from pg_dump to recreate things after
> doing "DROP VIEW ... CASCADE". But the pg_dump schema output is only
> approximately sorted by dependencies, and "create table..." is
> sprinkled all through it. That means I have to carefully comb through
> and select the pieces I need.
>
> Is there a way to just script the view definitions, then the
> functions? Or maybe I'm looking at it the wrong way?

Last place we worked we had a little plpgsql script to drop all views.
How much and what kind of control you wanna build for that is up to
you. My guess is that this is an operation done during downtime, so
as not to impact users and allow for the code changes in the
application to be updated too. Or at least preceding it, and so you
can just dump all views and recreate them.

Then just have all your view create script ready to run. I'd keep
that as the standard, not what comes out of pg_dump's schema output.
Changes don't go into the test database or higher without going
through the view creation process.