Postgres: Replace a view that has dependencies
I haven't found any neat solution for this, so I'm wondering if someone else stumbled upon something similar.
In Postgres, I have a view MyViewA
that is in use MyViewB
that is in use MyViewC
. As a performance improvement, I would like to materialize view A, but I cannot find an easy way to do this without dropping and redefining the entire dependency sequence.
That is, ideally, I would like to do this:
REPLACE MATERIALIZED VIEW MyViewA AS SELECT...
But, unfortunately, it does not allow the Postgres REPLACE
and MATERIALIZED
in the same context. I only tried DROP VIEW
, and then CREATE MATERIALIZED VIEW
, but that of course makes Postgres complain that itcannot drop view MyViewA because other objects depend on it
Any advice?
source to share
One way to do this is to create a temporary view, change MyViewB
to use that, override the original view and change MyViewB
back to the original, or if there are MyViewA
no prompts, just rename it, define a new one, and change MyViewB
.
So, let's say the original is (very simplified):
CREATE VIEW MyViewA AS SELECT col1, col2, col3 FROM table;
CREATE VIEW MyViewB AS SELECT col1, col2 FROM MyViewA where col3=1;
CREATE VIEW MyViewC AS SELECT col1 FROM MyViewC where col2='test';
Then you can change MyViewA
like this:
ALTER VIEW MyViewA RENAME TO MyViewTmp;
CREATE OR REPLACE VIEW MyViewB AS SELECT col1, col2 FROM MyViewTmp where col3=1;
CREATE MATERIALIZED VIEW MyViewA AS SELECT col1, col2, col3 FROM table;
CREATE OR REPLACE VIEW MyViewB AS SELECT col1, col2 FROM MyViewA where col3=1;
DROP VIEW MyViewTmp;
This way you don't need to handle dependent views.
source to share