3 CREATE TABLE utils.deps_saved_ddl (
5 view_schema character varying(255),
6 view_name character varying(255),
8 CONSTRAINT deps_saved_ddl_pkey PRIMARY KEY (id)
11 CREATE OR REPLACE FUNCTION utils.save_and_drop_views (p_view_schema name, p_view_name name)
24 FROM ( WITH RECURSIVE recursive_deps (
40 recursive_deps.depth + 1
43 ref_nsp.nspname ref_schema,
44 ref_cl.relname ref_name,
45 rwr_cl.relkind dep_type,
46 rwr_nsp.nspname dep_schema,
47 rwr_cl.relname dep_name
50 JOIN pg_class ref_cl ON dep.refobjid = ref_cl.oid
51 JOIN pg_namespace ref_nsp ON ref_cl.relnamespace = ref_nsp.oid
52 JOIN pg_rewrite rwr ON dep.objid = rwr.oid
53 JOIN pg_class rwr_cl ON rwr.ev_class = rwr_cl.oid
54 JOIN pg_namespace rwr_nsp ON rwr_cl.relnamespace = rwr_nsp.oid
57 AND dep.classid = 'pg_rewrite'::regclass) deps
58 JOIN recursive_deps ON deps.ref_schema = recursive_deps.obj_schema
59 AND deps.ref_name = recursive_deps.obj_name
60 WHERE (deps.ref_schema != deps.dep_schema
61 OR deps.ref_name != deps.dep_name))
78 IF v_curr.obj_type = 'v' THEN
79 INSERT INTO utils.deps_saved_ddl (view_schema, view_name, ddl_to_run)
83 'CREATE VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || view_definition
85 information_schema.views
87 table_schema = v_curr.obj_schema
88 AND table_name = v_curr.obj_name;
89 EXECUTE 'DROP VIEW' || ' ' || v_curr.obj_schema || '.' || v_curr.obj_name;
95 CREATE OR REPLACE FUNCTION utils.restore_views (p_view_schema character varying, p_view_name character varying)
110 view_schema = p_view_schema
111 AND view_name = p_view_name
116 EXECUTE v_curr.ddl_to_run;
117 DELETE FROM utils.deps_saved_ddl
118 WHERE id = v_curr.id;
121 -- keep looping, but please check for errors or remove left overs to handle manually