依赖性跟踪功能

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

也许有人有一些功能？

下一步......（我之前的回答的延续）。

功能save_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();