saving and rebuilding nested (materialized) views in postgres

rebuild_views.sql

create table saved_dependencies_ddl
(
  deps_id serial primary key,
  deps_view_schema varchar(255),
  deps_view_name varchar(255),
  dependency_name varchar(255),
  deps_ddl_to_run text
);

create or replace function save_and_drop_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
$$
declare
  v_curr record;
declare v_curr_idx record;
begin
for v_curr in
(
  select obj_schema, obj_name, obj_type from
  (
  with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
  (
    select p_view_schema, p_view_name, NULL::varchar, 0
    union
    select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_deps.depth + 1 from
    (
      select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
      rwr_cl.relkind dep_type,
      rwr_nsp.nspname dep_schema,
      rwr_cl.relname dep_name
      from pg_depend dep
      join pg_class ref_cl on dep.refobjid = ref_cl.oid
      join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
      join pg_rewrite rwr on dep.objid = rwr.oid
      join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid
      join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid
      where dep.deptype = 'n'
      and dep.classid = 'pg_rewrite'::regclass
    ) deps
    join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name
    where (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name)
  )
  select obj_schema, obj_name, obj_type, depth
  from recursive_deps
  where depth > 0
  ) t
  group by obj_schema, obj_name, obj_type
  order by max(depth) desc
) loop

    for v_curr_idx in (
      SELECT table_name, index_name, is_unique, index_keys
      FROM (
        SELECT
          idx.indrelid :: REGCLASS AS table_name,
          i.relname                AS index_name,
          idx.indisunique          AS is_unique,
          idx.indisprimary         AS is_primary,
          am.amname                AS index_type,
              ARRAY(
                  SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE)
                  FROM
                    generate_subscripts(idx.indkey, 1) AS k
                  ORDER BY k
              ) AS index_keys
        FROM pg_index AS idx
          JOIN pg_class AS i
            ON i.oid = idx.indexrelid
          JOIN pg_am AS am
            ON i.relam = am.oid
          JOIN pg_namespace AS NS
            ON i.relnamespace = NS.OID
      ) index_data
      WHERE index_data.table_name::text = v_curr.obj_schema || '.' || v_curr.obj_name
    ) loop
      if v_curr_idx.is_unique then
        insert into saved_dependencies_ddl(deps_view_schema, deps_view_name, dependency_name, deps_ddl_to_run)
        select p_view_schema,
          p_view_name,
          p_view_schema || '.' || v_curr_idx.index_name,
          'CREATE UNIQUE INDEX ON '                     ||
            v_curr_idx.table_name                       ||
            ' ('                                        ||
            array_to_string(v_curr_idx.index_keys, ',') ||
            ')';
      else
        insert into saved_dependencies_ddl(deps_view_schema, deps_view_name, dependency_name, deps_ddl_to_run)
        select p_view_schema,
          p_view_name,
          p_view_schema || '.' || v_curr_idx.index_name,
          'CREATE INDEX ON '                            ||
            v_curr_idx.table_name                       ||
            ' ('                                        ||
            array_to_string(v_curr_idx.index_keys, ',') ||
            ')';
      end if;
    end loop;

  insert into saved_dependencies_ddl(deps_view_schema, deps_view_name, dependency_name, deps_ddl_to_run)
  select p_view_schema, p_view_name, n.nspname || '.' || c.relname,'COMMENT ON ' ||
  case
  when c.relkind = 'v' then 'VIEW'
  when c.relkind = 'm' then 'MATERIALIZED VIEW'
  else ''
  end
  || ' ' || n.nspname || '.' || c.relname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
  join pg_description d on d.objoid = c.oid and d.objsubid = 0
  where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;

  insert into saved_dependencies_ddl(deps_view_schema, deps_view_name, dependency_name, deps_ddl_to_run)
  select p_view_schema,
    p_view_name,
    n.nspname || '.' || c.relname,
    'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
  from pg_class c
  join pg_attribute a on c.oid = a.attrelid
  join pg_namespace n on n.oid = c.relnamespace
  join pg_description d on d.objoid = c.oid and d.objsubid = a.attnum
  where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;

  insert into saved_dependencies_ddl(deps_view_schema, deps_view_name, dependency_name, deps_ddl_to_run)
  select p_view_schema, p_view_name, table_schema || '.' || table_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO ' || grantee
  from information_schema.role_table_grants
  where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;

  if v_curr.obj_type = 'v' then
    insert into saved_dependencies_ddl(deps_view_schema, deps_view_name, dependency_name, deps_ddl_to_run)
    select p_view_schema,
      p_view_name,
      v_curr.obj_schema || '.' || v_curr.obj_name,
      'CREATE VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || view_definition
    from information_schema.views
    where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
  elsif v_curr.obj_type = 'm' then
    insert into saved_dependencies_ddl(deps_view_schema, deps_view_name, dependency_name, deps_ddl_to_run)
    select p_view_schema,
      p_view_name,
      v_curr.obj_schema || '.' || v_curr.obj_name,
      'CREATE MATERIALIZED VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || definition
    from pg_matviews
    where schemaname = v_curr.obj_schema and matviewname = v_curr.obj_name;
  end if;

  RAISE NOTICE 'dropping %.%', v_curr.obj_schema, v_curr.obj_name;
  execute 'DROP ' ||
  case 
    when v_curr.obj_type = 'v' then 'VIEW'
    when v_curr.obj_type = 'm' then 'MATERIALIZED VIEW'
  end
  || ' ' || v_curr.obj_schema || '.' || v_curr.obj_name;

end loop;
end;
$$
LANGUAGE plpgsql;


create or replace function deps_restore_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
$$
declare
  v_curr record;
begin
for v_curr in
(
  select deps_ddl_to_run, dependency_name
  from saved_dependencies_ddl
  where deps_view_schema = p_view_schema and deps_view_name = p_view_name
  order by deps_id desc
) loop
  RAISE NOTICE 'rebuilding %...', v_curr.dependency_name;
  execute v_curr.deps_ddl_to_run;
  RAISE NOTICE 'finished rebuilding %.', v_curr.dependency_name;
end loop;
delete from saved_dependencies_ddl
where deps_view_schema = p_view_schema and deps_view_name = p_view_name;
end;
$$
LANGUAGE plpgsql;

original source:

https://gist.github.com/mateuszwenus/11187288