Dependency Tracking function

问题:

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?

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

回答2:

Table pg_depend contains all necessary informations, but it is not so easy to interpret them. Here you have sketch recursive function to retrieve dependencies of pg_class object in text format. You can tune up the function to your needs (and show us 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, ' ');