Dependency Tracking Function
I'm just wondering if anyone knows how to automate the creation of views after launch DROP ... CASCADE
? Now I first want to drop the view with the classic expression DROP VIEW myview
, and if I can’t drop the view because other objects are still dependent on it, then check all the object names that postgres publish and keep them created, and then I run drop with cascade. Sometimes it looks like more than a dozen objects. But maybe you have an idea to solve this problem in a more automated way?
Maybe someone has some kind of function?
source to share
Next step ... (continuation of my previous answer).
Function save_views (objectname text) stores representations depending on objectname (or table) in Table sohranennye_views .
Function restore_views () restores the presentation of the table sohranennye_views .
create or replace function save_views_oid(objectid oid)
returns void language plpgsql as $$
declare
r record;
begin
for r in
select distinct c.oid, c.relname, n.nspname
from pg_depend d
join pg_rewrite w on w.oid = d.objid
join pg_class c on c.oid = w.ev_class
join pg_namespace n on n.oid = c.relnamespace
where d.refclassid = 'pg_class'::regclass
and d.classid = 'pg_rewrite'::regclass
and d.refobjid = objectid
and c.oid <> objectid
loop
insert into saved_views values (
'CREATE VIEW ' || r.nspname || '.' || r.relname ||
' AS ' || pg_get_viewdef(r.oid, 'f'));
perform save_views_oid(r.oid);
end loop;
end; $$;
create or replace function save_views(objectname text)
returns void language plpgsql as $$
begin
create table if not exists saved_views(viewbody text);
truncate saved_views;
perform save_views_oid(objectname::regclass);
end; $$;
create or replace function restore_views()
returns void language plpgsql as $$
declare
viewtext text;
begin
for viewtext in
select viewbody from saved_views
loop
execute viewtext;
end loop;
drop table saved_views;
end; $$;
Test:
select save_views('my_view'); -- may be save_views('my_schema.my_view');
select * from saved_views;
Using:
select save_views('my_view');
drop view my_view cascade;
create view my_view as ...
select restore_views();
source to share
The pg_depend table contains all the information you need, but they are not easy to interpret. Here you have a recursive sketch function to extract the dependencies of the pg_class object in text format. You can customize the function according to your needs (and show us the results :).
create or replace function dependency
(class_id regclass, obj_id regclass, obj_subid integer, dep_type "char")
returns setof text language plpgsql as $$
declare
r record;
begin
return query
select pg_describe_object(class_id, obj_id, obj_subid)
|| ' ('|| dep_type|| ')';
for r in
select classid, objid, objsubid, deptype
from pg_depend
where class_id = refclassid
and obj_id = refobjid
and (obj_subid = refobjsubid or obj_subid = 0)
loop
return query select dependency(r.classid, r.objid, r.objsubid, r.deptype);
end loop;
end; $$;
use:
select dependency('pg_class'::regclass, 'my_view'::regclass, 0, ' ');
select dependency('pg_class'::regclass, 'my_table'::regclass, 0, ' ');
source to share