How to intelligently work with views that depend on other views

On 08/06/2015 01:44 PM, W. Matthew Wilson wrote:

I have a bunch of views, and some views use data from other views.

For example, view A might get used by view B and view B gets used by view C.

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.

This is getting frustrating! When I want to change how I make view A,
I have to drop view A cascade, and then view B and view C are dropped.

Then I have to remember to re-create B and C after I rewrite A.

There's likely a better solution...

What is it?

Use some sort of schema versioning and management tool. I have been
using Sqitch(http://sqitch.org/) and have found it very useful for this
sort of thing. There is some setup overhead, but after that going
forward and backwards in your schema management becomes fairly easy. As
a bonus you get a nice record of what you have done and if you write
good comments, why you did it:)

GO POSTGRESQL!

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The best solution, IMHO, is don't create views that depend on other views.
Although, it makes it easier in some cases, you pay the price in
maintenance, as you have already found out. In addition, there is also the
security problem that comes into play. EG: a user is allowed access to view
c, but should not be allowed to see columns of table 1, which
are referenced by view a.

Much better to just make each view a stand alone.

On Thu, Aug 6, 2015 at 4:44 PM, W. Matthew Wilson <matt@tplus1.com> wrote:

I have a bunch of views, and some views use data from other views.

For example, view A might get used by view B and view B gets used by view
C.

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.

This is getting frustrating! When I want to change how I make view A,
I have to drop view A cascade, and then view B and view C are dropped.

Then I have to remember to re-create B and C after I rewrite A.

There's likely a better solution...

What is it?

GO POSTGRESQL!

--
W. Matthew Wilson
matt@tplus1.com
http://tplus1.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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@tplus1.com
<mailto:matt@tplus1.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@computer.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@tplus1.com
<mailto:matt@tplus1.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.

Correction, as Mr. Tober suggested.

On Thu, Aug 6, 2015 at 8:34 PM, Melvin Davidson <melvin6925@gmail.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@computer.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@tplus1.com
<mailto:matt@tplus1.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.