Re: manage changes to views having depencies

Eric Worden 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?
>

I've run into the situation similar to yours but with views and
foreign key dependent on tables. Same process probably would work
for you.

I use pgAdmin III to run my desired change script, which might
include a DELETE...CASCADE or ALTER ... command(s), wrapped
inside a BEGIN ... ROLLBACK block (since this may require a
number of iterations). The messages in the pgAdmin output pane
identify dependencies specifically related to the changes you
intend to make.

Then for each dependency, I build out my script by inserting it
in between the DROP ... CREATE commands identified for each
dependency in each iteration of the process until all the
dependencies are resolved.

This is not necessarily what I would think of as ideal, since it
is not really very well automated to the extent I'd like to see,
but it has worked, and it has proven "efficient enough" for a
process that is not a routine, everyday task.