依赖关系跟踪功能 [英] Dependency Tracking function

问题描述

我只是想知道是否有人知道如何在运行 DROP ... CASCADE 后自动化视图创建？
现在我试图删除视图，首先与经典的 DROP VIEW myview 语句，如果我不能删除视图，因为其他对象仍然依赖它，然后检查所有对象名称postgres列出并保存他们的创建，然后我运行drop与级联。有时它像十几个对象。但也许你有一些想法以更自动化的方式处理这个问题？

也许任何人都有一些功能？

解决方案

下一步...（继续我上一个答案）。

根据 saved_views 表中的对象名称（视图或表格）存储视图

。

$ b

函数 restore_views（）从表格 saved_views 中还原视图。

 创建或替换函数save_views_oid（objectid oid）
返回void language plpgsql as $$ 
 declare 
r record; 
 begin 
 for r in 
 select distinct c.oid，c.relname，n.nspname 
 from pg_depend d 
连接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 
其中d.refclassid ='pg_class':: regclass 
和d.classid ='pg_rewrite':: regclass 
和d.refobjid = objectid 
和c.oid<> objectid 
 loop 
 insert into saved_views values（
'CREATE VIEW'|| r.nspname ||'。'|| r.relname || 
'AS'|| pg_get_viewdef （r.oid，'f'））; 
执行save_views_oid（r.oid）; 
 end loop; 
 end; $$; 
 
创建或替换函数save_views（objectname text）
返回void language plpgsql as $$ 
 begin 
如果不存在则创建表saved_views（viewbody text）; 
 truncate saved_views; 
执行save_views_oid（objectname :: regclass）; 
 end; $$; 
 
创建或替换函数restore_views（）
返回void language plpgsql as $$ 
 declare 
 viewtext text; 
 begin 
 for viewtext 
 select viewbody from saved_views 
 loop 
执行viewtext; 
 end loop; 
 drop table saved_views; 
 end; $$; 
  

测试：

 code> select save_views（'my_view'）; - 可能是save_views（'my_schema.my_view'）; 
 select * from saved_views; 
  

使用：

 code> select save_views（'my_view'）; 
 drop view my_view cascade; 
 create view my_view as ... 
 select restore_views（）; 
  

I just wonder if anyone knows how to automatize views creation after running DROP ... CASCADE? Now I'm trying to drop view at first with classic DROP VIEW myview statement and if I cannot drop the view because other objects still depend on it then checking out all the objects names that postgres lists and save their creates and then I run drop with cascade. Sometimes it's like over a dozen objects. But maybe you have got some idea to handle this issue in more automated way?

Maybe anybody has got some function?

解决方案

Next step... (continuation of my previous answer).

function save_views(objectname text) stores views depending on objectname (view or table) in table saved_views.

function restore_views() restores views from table saved_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;

Use:

select save_views('my_view'); 
drop view my_view cascade;
create view my_view as ...
select restore_views();

这篇关于依赖关系跟踪功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！