· 9 years ago · Sep 29, 2016, 12:18 AM
1create table if not exists deps_saved_ddl
2(
3 deps_id serial primary key,
4 deps_view_schema varchar(255),
5 deps_view_name varchar(255),
6 deps_ddl_to_run text,
7 deps_type char
8);
9
10--ALTER TABLE ONLY deps_saved_ddl ADD COLUMN deps_type char;
11
12create or replace function deps_save_and_drop_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
13$$
14declare
15 v_curr record;
16begin
17for v_curr in
18(
19 select obj_schema, obj_name, obj_type from
20 (
21 with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
22 (
23 select p_view_schema, p_view_name, null::varchar, 0
24 union
25 select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_deps.depth + 1 from
26 (
27 select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
28 rwr_cl.relkind dep_type,
29 rwr_nsp.nspname dep_schema,
30 rwr_cl.relname dep_name
31 from pg_depend dep
32 join pg_class ref_cl on dep.refobjid = ref_cl.oid
33 join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
34 join pg_rewrite rwr on dep.objid = rwr.oid
35 join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid
36 join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid
37 where dep.deptype = 'n'
38 and dep.classid = 'pg_rewrite'::regclass
39 ) deps
40 join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name
41 where (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name)
42 )
43 select obj_schema, obj_name, obj_type, depth
44 from recursive_deps
45 where depth > 0
46 ) t
47 group by obj_schema, obj_name, obj_type
48 order by max(depth) desc
49) loop
50
51 insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run, deps_type)
52 select p_view_schema, p_view_name, 'COMMENT ON ' ||
53 case
54 when c.relkind = 'v' then 'VIEW'
55 when c.relkind = 'm' then 'MATERIALIZED VIEW'
56 else ''
57 end
58 || ' ' || n.nspname || '.' || c.relname || ' IS ''' || replace(d.description, '''', '''''') || ''';', 'c'
59 from pg_class c
60 join pg_namespace n on n.oid = c.relnamespace
61 join pg_description d on d.objoid = c.oid and d.objsubid = 0
62 where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;
63
64 insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run, deps_type)
65 select p_view_schema, p_view_name, 'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname || ' IS ''' || replace(d.description, '''', '''''') || ''';', 'c'
66 from pg_class c
67 join pg_attribute a on c.oid = a.attrelid
68 join pg_namespace n on n.oid = c.relnamespace
69 join pg_description d on d.objoid = c.oid and d.objsubid = a.attnum
70 where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;
71
72 insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run, deps_type)
73 select p_view_schema, p_view_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '."' || table_name || '" TO ' || grantee, 'g'
74 from information_schema.role_table_grants
75 where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
76
77 insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run, deps_type)
78 select p_view_schema, p_view_name, pg_get_indexdef(idx.oid), 'i'
79 from pg_index ind
80 join pg_class idx on idx.oid = ind.indexrelid
81 join pg_class tbl on tbl.oid = ind.indrelid
82 left join pg_namespace ns on ns.oid = tbl.relnamespace
83 where tbl.relname = v_curr.obj_name and ns.nspname = v_curr.obj_schema;
84
85 if v_curr.obj_type = 'v' then
86 insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run, deps_type)
87 select p_view_schema, p_view_name, 'CREATE VIEW ' || v_curr.obj_schema || '."' || v_curr.obj_name || '" AS ' || view_definition, 'v'
88 from information_schema.views
89 where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
90 elsif v_curr.obj_type = 'm' then
91 insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run, deps_type)
92 select p_view_schema, p_view_name, 'CREATE MATERIALIZED VIEW ' || v_curr.obj_schema || '."' || v_curr.obj_name || '" AS ' || definition, 'm'
93 from pg_matviews
94 where schemaname = v_curr.obj_schema and matviewname = v_curr.obj_name;
95 end if;
96
97 execute 'DROP ' ||
98 case
99 when v_curr.obj_type = 'v' then 'VIEW'
100 when v_curr.obj_type = 'm' then 'MATERIALIZED VIEW'
101 end
102 || ' ' || v_curr.obj_schema || '."' || v_curr.obj_name || '"';
103
104end loop;
105
106-- save foreign keys
107for v_curr in
108(
109 SELECT ref_nsp.nspname ref_schema, ref_cl.relname ref_name, const.conname conname, pg_catalog.pg_get_constraintdef(const.oid, true) as condef
110 FROM pg_catalog.pg_constraint const
111 JOIN pg_class ref_cl on const.conrelid = ref_cl.oid
112 JOIN pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
113 join pg_class orig_cl on const.confrelid = orig_cl.oid
114 join pg_namespace orig_nsp on orig_cl.relnamespace = orig_nsp.oid
115
116 WHERE
117 orig_cl.relname = p_view_name AND
118 orig_nsp.nspname = p_view_schema AND
119 const.contype = 'f'
120) loop
121 insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run, deps_type)
122 select p_view_schema, p_view_name,
123 'ALTER TABLE ' || v_curr.ref_schema || '."' || v_curr.ref_name || '" ADD CONSTRAINT "' || v_curr.conname || '" ' || v_curr.condef || ';', 'f';
124 execute 'ALTER TABLE ' || v_curr.ref_schema || '."' || v_curr.ref_name || '" DROP CONSTRAINT "' || v_curr.conname || '"';
125end loop;
126end;
127$$
128LANGUAGE plpgsql;
129
130create or replace function deps_restore_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
131$$
132declare
133 v_curr record;
134begin
135for v_curr in
136(
137 select deps_ddl_to_run
138 from deps_saved_ddl
139 where deps_view_schema = p_view_schema and deps_view_name = p_view_name
140 order by deps_id desc
141) loop
142 execute v_curr.deps_ddl_to_run;
143end loop;
144delete from deps_saved_ddl
145where deps_view_schema = p_view_schema and deps_view_name = p_view_name;
146end;
147$$
148LANGUAGE plpgsql;