PostgreSQL: How to handle table and view dependencies

save_restore_dependencies.sql

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

create or replace function deps_save_and_drop_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
$$
declare
  v_curr 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

  insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
  select p_view_schema, p_view_name, '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 deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
  select p_view_schema, p_view_name, '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 deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
  select p_view_schema, p_view_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 deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    select p_view_schema, p_view_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 deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    select p_view_schema, p_view_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;
  
  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 
  from deps_saved_ddl
  where deps_view_schema = p_view_schema and deps_view_name = p_view_name
  order by deps_id desc
) loop
  execute v_curr.deps_ddl_to_run;
end loop;
delete from deps_saved_ddl
where deps_view_schema = p_view_schema and deps_view_name = p_view_name;
end;
$$
LANGUAGE plpgsql;

Thanks for this gist. It's really helpful + the comments.

I noticed that it was skipping the recreation of materialized view permissions which are not stored in the information schema and added the below to capture them during object recreation

-- Save materialized views permissions. Why? Their information will be missing in the information schema since they are not SQL standard SQL objects
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'GRANT ' || priviliges_from_acl(s[2]) || ' ON ' || n.nspname || '.' || c.relname || ' TO ' || coalesce(nullif(s[1], ''), 'public')
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
join pg_roles r on r.oid = relowner,
unnest(coalesce(relacl::text[], format('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl,
regexp_split_to_array(acl, '=|/') s
where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name;

Thanks a lot, this has been bothering me for quite a while until now :)
This is the most intuitive version to adress the underlying problem amongst various other approaches (like manually just dropping all views and restoring them from a bkp or sql file)

Thanks for this script. I have made some changes to make it fit my framework (which I won't bother your with). Also I like to use the FORMAT function instead of string concatenation with '||'.

And I have added code to capture triggers too. This code snippet can be added in the FOR LOOP preferable on top at line 47.

-- Watch the regexp_replace where I insert the referencing tables for FOR EACH STATEMENT. Of course this is my own standard.
-- Use of GROUP BY to aggregate "event_manipulation" field into "INSERT OR UPDATE OR DELETE" as far as applicable.

INSERT into hdsa.deps_saved_ddl(schemaname, viewname, script)
  SELECT p_view_schema, p_view_name,
     REGEXP_REPLACE( 
         FORMAT('CREATE OR REPLACE TRIGGER %s %s %s ON %s.%s FOR EACH %s %s',
            trigger_name, action_timing, STRING_AGG(event_manipulation, ' OR '), 
            event_object_schema, event_object_table, action_orientation, action_statement),
        '\sFOR\sEACH\sSTATEMENT\s', ' REFERENCING  NEW TABLE AS new_table  OLD TABLE AS old_table  FOR EACH STATEMENT ', 'i')
  FROM information_schema.triggers
  WHERE event_object_schema ILIKE v_curr.obj_schema
    AND event_object_table ILIKE v_curr.obj_name
    AND p_view_schema NOT ILIKE v_curr.obj_schema
  GROUP BY trigger_name, action_timing, event_object_schema, event_object_table, action_orientation, action_statement;