Home > mailing lists

Re: Alter view with dependence without drop view! - Mailing list pgsql-general

From	Ken Tanzer
Subject	Re: Alter view with dependence without drop view!
Date	January 31, 2018 03:50:58
Msg-id	CAD3a31Wf+Qvs1Oifk6gJjhKrUrR6UW4FADbQgEwUXOVsQ38vKw@mail.gmail.com Whole thread Raw
In response to	Re: Alter view with dependence without drop view! (Melvin Davidson <melvin6925@gmail.com>)
List	pgsql-general

Tree view

I want make change in one view that have dependence view's, so when i try to make change i'm block because of this, what is the best solution??

Just to be clear, there are some changes you can make, some you can't. Basically you can't change the definition of existing columns in the view. But if you want to change the logic that generates those columns, or add new columns at the end, you can do that with CREATE OR REPLACE VIEW ...

On Tue, Jan 30, 2018 at 10:12 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

On Tue, Jan 30, 2018 at 12:48 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jan 30, 2018 at 8:34 AM, btober@computer.org <btober@broadstripe.net> wrote:

When this procedure got old, I started using a script created using pg_dump and pg_restore, as initially outlined here:

Yeah, the short answer is PostgreSQL doesn't make it possible to edit "middle" views without having the code on hand for all dependent views so you can recreate them. You either maintain those views and order manually or you rely on pg_dump to figure it out for you (the former, with version control, is highly recommended).

I agree it's best to keep the definitions of the view yourself--among other things it lets you keep comments. But you can also generate a perfectly-functional definition of a view with \d+.

Just a side note, it is not a good practice to create views based on other views.
Multiple reasons are stated here:

https://dba.stackexchange.com/questions/5487/is-nested-view-a-good-database-design

To summarize, you suffer performance degradation, columns names and the tables referenced become obscured and you incur the problems you are now experiencing.

That link you pointed to includes arguments both for and against. It seems to me that performance weighs against nested views, clarity can cut both ways, and nested views can help centralize and encapsulate business logic, avoiding needs for reuse and maintaining consistency. Whether they are a good idea or not depends on your goals, priorities and specific situation.

Cheers,

Ken