Notes

CREATE OR REPLACE FUNCTION rename_dependent_mvs(target_mv TEXT)
RETURNS void AS $$
DECLARE
dep RECORD;
old_name TEXT;
new_name TEXT;
BEGIN
FOR dep IN
SELECT
c.oid,
n.nspname AS schema_name,
c.relname AS mv_name
FROM
pg_rewrite r
JOIN pg_class c ON r.ev_class = c.oid
JOIN pg_depend d ON r.oid = d.objid
JOIN pg_class dep_c ON d.refobjid = dep_c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
dep_c.relname = target_mv
AND c.relkind = 'm' -- 'm' = materialized view
LOOP
old_name := dep.mv_name;

-- Check if it has _new in its name
IF old_name LIKE '%_new%' THEN
new_name := replace(old_name, '_new', '');

-- Build dynamic SQL to rename the MV
EXECUTE format('ALTER MATERIALIZED VIEW %I.%I RENAME TO %I',
dep.schema_name, old_name, new_name);

RAISE NOTICE 'Renamed % to %', old_name, new_name;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;