How to alter materialized view with dependent views

user_5476786 0
Viewed 3896

I am currently having a series of materialized views View #1 and View #2 which I use for reporting.

Using PGAdmin, I want to change the code of Materialized View #1 (just change the where clause, the structure of the view remains the same), but Materialized View #2 is generated from Materialized View #1.

Is there a way to defer any of the checks so that I can make a change without dropping every subsequent view?

sql-view materialized-views postgresql
1 Answers

Nyes...

There is a solution that may not be suitable in every cases. It might be simpler to rerun the creation script of all dependent objects.

So, while the query of the materialized view cannot change without dropping the materialized view first, nothing prevents you from storing the said query in a regular view, and to create a materialized view calling this regular view. You are then free to replace (update) the regular view and then you just have to refresh the materialized view.

create table test_table(id int, txt text);
insert into test_table values (1,'one'),(2,'two');

create or replace view test_view
AS 
 select id as v_id, 
        txt as v_txt
 from test_table;
 
 
create materialized view test_mat_view
AS
 select v_id, v_txt from test_view;
 
select * from test_mat_view; 
 
  v_id | v_txt
------+-------
    1 | one
    2 | two
(2 rows)
 
create or replace view test_view
AS 
 select id as v_id, 
        'constant' as v_txt
 from test_table;
 
REFRESH materialized view test_mat_view;
  
select * from test_mat_view; 
 
 v_id |  v_txt
------+----------
    1 | constant
    2 | constant
 
drop table test_table cascade;
user_76355690
Related