Notes

CREATE OR REPLACE FUNCTION recreate_dependent_mvs(old_base_mv TEXT, new_base_mv TEXT)
RETURNS VOID AS $$
DECLARE
rec RECORD;
dep_mv_name TEXT;
new_mv_name TEXT;
mv_definition TEXT;
BEGIN
FOR rec IN
SELECT
c.oid,
c.relname AS dependent_mv_name
FROM
pg_depend d
JOIN
pg_rewrite r ON r.oid = d.objid
JOIN
pg_class c ON c.oid = r.ev_class
WHERE
d.refobjid = old_base_mv::regclass
AND c.relkind = 'm' -- 'm' = materialized view
LOOP
dep_mv_name := rec.dependent_mv_name;
new_mv_name := dep_mv_name || '_new';

-- Get the SQL definition of the dependent MV
SELECT pg_get_viewdef(rec.oid, true) INTO mv_definition;

-- Replace old_base_mv with new_base_mv in the definition
mv_definition := replace(mv_definition, old_base_mv, new_base_mv);

-- Construct CREATE MATERIALIZED VIEW SQL
EXECUTE format('CREATE MATERIALIZED VIEW %I AS %s', new_mv_name, mv_definition);

RAISE NOTICE 'Created new MV: %', new_mv_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;


SELECT recreate_dependent_mvs('base_mv', 'base_mv_new');