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

As Mr. Wilson suggested, you can use pg_dump to extract the views, but I
also suggest downloading and installing pg_extractor, which uses pg_dump
but allows more options to dump just the objects you need. IE: views,
functions, etc.

Here's the url to download.
https://github.com/omniti-labs/pg_extractor

These url's give more detail about it.

http://www.keithf4.com/pg_extractor/

https://www.youtube.com/watch?v=a7P4TV8xUOM

On Thu, Aug 6, 2015 at 7:41 PM, Berend Tober <btober(at)computer(dot)org> wrote:

> 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
>
>
>
>
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.