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

Correction, as Mr. Tober suggested.

On Thu, Aug 6, 2015 at 8:34 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

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

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