Re: How to intelligently work with views that depend on other views

Melvin Davidson wrote:
> The best solution, IMHO, is don't create views that depend on other
> views. ...
>
> Much better to just make each view a stand alone.

Seconding Mr. Davidson's advice.

But, given that you are in the situation, here is a workable alternative:

Matthew Wilson
>
> On Thu, Aug 6, 2015 at 4:44 PM, W. Matthew Wilson <matt(at)tplus1(dot)com
> <mailto:matt(at)tplus1(dot)com>> wrote:
>
> I have a bunch of views, and some views use data from other views.
>
> ...
> Several times now, as I got further into the project, I've changed how
> I make some views and I've had to redefine not just that view, but all
> the ones that depend on it.
>

1. Dump the data base:

pg_dump mydatabase -U postgres -Fc > mydatabase.dump

2. Create a list of all data base objects from the dump file

pg_restore -l mydatabase.dump > mydatabase.list

3. Edit the list file and delete all rows except those for your views A,
B, and C. Make sure you leave the lines corresponding to those views in
the order in which they appear in the list file, as the following step
will then have commands in the right dependency order.

4. Generate a SQL command file, based on the dump and the edited list file:

pg_restore -C -L mydatabase.list mydatabase.dump > sql

5. Edit your view definitions in the SQL command file.

6. Run the SQL command file:

psql mydatabase -U postgres -f sql