Обсуждение: manage changes to views having depencies

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?

On Wed, Dec 31, 2008 at 9:57 PM, Eric Worden <worden.eric@gmail.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.

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.