Hello,
As part of analysis pipelines I'm dealing with stacks/trees of views — views selecting
from other views, often recombining it with table data — which at times undergo heavy
development. This often requires changing/discarding/renaming/… a view or many views
somewhere in the middle of the stack. Depending on whether the type signature of the view
is changing, a CREATE OR REPLACE to mutate views "in-place" may not be possible, so doing
refactoring often requires dropping & recreating views dependent on the mutated views.
Are there tools out there which allow for easily doing such work - rebuilding
parts of the dependency-tree of views as needed? I'm looking for something more convenient
than "put your view DDL statements in a carefully ordered & manually reference-checked
.sql file". I also want to have the state of the view tree outside of the database and
maintain/version it in concert with application code.
Furthermore, some of the views in the tree are materialized, and I'd like to be able to
issue some sort of "cascading refresh" at my convenience (so, not through a trigger), that
is, refresh all materialized views that directly or indirectly depend on data sourced from
certain tables (by-table resolution would be high enough, by-column resolution is not
necessary), and do so in dependency-order. Are there tools out there that allow me to do
such?
I'm currently working on my own spin on solving this mundane-sounding problem, and I'm
curious towards any existing approaches and any PostgreSQL-native primitives that may
be of aid. Please share your thoughts and comments!
Thanks and regards, Wicher