3 create table utils.deps_saved_ddl
6 view_schema character varying(255),
7 view_name character varying(255),
9 CONSTRAINT deps_saved_ddl_pkey PRIMARY KEY (id)
12 create or replace function utils.save_and_drop_views(p_view_schema name, p_view_name name)
23 select obj_schema, obj_name, obj_type from
25 with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
27 select p_view_schema::name, p_view_name, null::varchar, 0
29 select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_deps.depth + 1 from
31 select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
32 rwr_cl.relkind dep_type,
33 rwr_nsp.nspname dep_schema,
34 rwr_cl.relname dep_name
36 join pg_class ref_cl on dep.refobjid = ref_cl.oid
37 join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
38 join pg_rewrite rwr on dep.objid = rwr.oid
39 join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid
40 join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid
41 where dep.deptype = 'n'
42 and dep.classid = 'pg_rewrite'::regclass
44 join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name
45 where (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name)
47 select obj_schema, obj_name, obj_type, depth
51 group by obj_schema, obj_name, obj_type
52 order by max(depth) desc
54 if v_curr.obj_type = 'v' then
55 insert into utils.deps_saved_ddl(view_schema, view_name, ddl_to_run)
56 select p_view_schema, p_view_name, 'CREATE VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || view_definition
57 from information_schema.views
58 where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
60 execute 'DROP VIEW' || ' ' || v_curr.obj_schema || '.' || v_curr.obj_name;
66 create or replace function utils.restore_views(p_view_schema character varying, p_view_name character varying)
77 from utils.deps_saved_ddl
78 where view_schema = p_view_schema and view_name = p_view_name
82 execute v_curr.ddl_to_run;
83 delete from utils.deps_saved_ddl where id = v_curr.id;
84 EXCEPTION WHEN OTHERS THEN
85 -- keep looping, but please check for errors or remove left overs to handle manually