Re: view management

On Nov 16, 2007 4:02 PM, Ed L. <pgsql(at)bluepolka(dot)net> wrote:
> On Friday 16 November 2007 1:57 pm, Ed L. wrote:
> > I have a question about view management...
> >
> > I often have need for views that reference views that
> > reference views, and so on. When I need to make a small
> > update to one of the views, I am faced with having to drop and
> > recreate all dependent views even if the driving change just
> > adds another column to the view, for example. I might have to
> > drop and recreate many tens of views just to make a change to
> > a single view. What a PITA. How do others manage this?
>
> And before you tell me all about DROP VIEW ... CASCADE, please
> note I'm not talking about that. I'm talking about the
> difficulties of having to recreate all views in the entire
> subtree of view dependencies just to change one minor aspect of
> an independent view.

you have to rig a build system. if you have a lot of views (which is
good), and keeping them up to date is a pain, you have to automate
their creation. simplest way to do that is to rig a build system
around sql scripts. when you create a view the first time, save it's
creation script in a .sql file and replay that when you need it. if
you like to get fancy, you can always work solutions around make, etc
on top of this. there are other tricks...for example you could grep
object dropped by the database and replay them.

avoid gui tools for heavy management...the are the antithesis of this
kind of approach.

merlin