PostgreSQL 命令，用于删除角色拥有的所有视图和函数

Question

PostgreSQL中是否有用于删除特定角色拥有的所有视图和函数的命令？我不明白所有内部表是如何连接在一起的，以便我可以创建这样的命令。

Answer 1

删除视图：

create function drop_views_by_owner(owner regrole, do_drop boolean default true)
returns setof text language plpgsql as $$
declare r text;
begin
    for r in 
        select format('%s.%s', nspname, relname)
        from pg_class c
        join pg_namespace n on n.oid = relnamespace
        where c.relowner = $1
        and relkind = 'v'
    loop
        if do_drop then
            execute format('drop view %s', r);
        end if;
        return next r;
    end loop;
end $$;

下降功能：

create function drop_functions_by_owner(owner regrole, do_drop boolean default true)
returns setof text language plpgsql as $$
declare r text;
begin
    for r in 
        select format('%s.%s(%s)', 
            nspname, proname, pg_get_function_identity_arguments(p.oid))
        from pg_proc p
        join pg_namespace n on n.oid = pronamespace
        where p.proowner = $1
    loop
        if do_drop then
            execute format('drop function %s', r);
        end if;
        return next r;
    end loop;
end $$;

如果第二个参数false则函数不会删除对象：

select drop_views_by_owner('newuser');            -- list views and drop them
select drop_views_by_owner('newuser', false);     -- only list views    
select drop_functions_by_owner('newuser');        -- list functions and drop them
select drop_functions_by_owner('newuser', false); -- only list functions        

Answer 2

如果您的用户只拥有视图和函数，您只需：

DROP OWNED BY your_role;

您必须为集群中用户拥有对象的每个数据库执行此操作。

您可能会发现此文档很有用： https://www.postgresql.org/docs/current/static/role-removal.html