数据库 – 依靠性跟踪成果

我只是想知道在运行DROP … CASCADE后是否有人知道怎样自动化视图建设？
此刻我起首实行行使经典的DROP VIEW myview语句删除视图,假如我不能删除视图,由于其他工具如故依靠于它,然后搜查postgres列出的全部工签字称并生涯它们的建设然后我运行drop with级联.偶然它就像十几个物体.但大概您有一些设法以更自动化的方法处理赏罚这个题目？

大概有人有一些成果？

办理要领

function save_views(objectname text)按照表saved_views中的objectname(视图或表)存储视图.

function restore_views()从表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; $$;

测试：

select save_views('my_view'); -- may be save_views('my_schema.my_view');
select * from saved_views;

行使：

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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!