Dependency Tracking function

Question

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?

Answer 1

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();