PostgreSQL 使用递归SQL 找出数据库对象之间的依赖关系 - 例如视图依赖

在当前schema下创建2个视图
=# create view v1 as select * from pglog;
=# create view v2 as select * from v1;

在其它schema下也创建一个视图
=# create schema sm1;
=# create view sm1.v1 as select * from pglog limit 10;

-- 注意下search_path，下面建的function都是只能在指定的search_path下访问到。

set search_path='public';

create or replace function get_dep_oids(oid) returns oid[] as $$
declare
  res oid[];
begin
  select array_agg(unnest::oid) into res from 
  (
    select unnest(regexp_matches(ev_action::text,':relid (\d+)', 'g')) from pg_rewrite where ev_class = $1 
  union 
    select unnest(regexp_matches(ev_action::text,':resorigtbl (\d+)','g')) from pg_rewrite where ev_class = $1 
  EXCEPT 
    select oid::text from pg_class where oid=$1 
  ) t;
return res;
end;
$$ language plpgsql strict;


=# select * from get_dep_oids('v1'::regclass);
 get_dep_oids 
──────────────
 {24971}
(1 row)

14:41:19 db: postgres@postgres, pid:54661
=# select * from get_dep_oids('v2'::regclass);
 get_dep_oids 
──────────────
 {24975}
(1 row)

=# select * from get_dep_oids('sm1.v1'::regclass);
 get_dep_oids 
──────────────
 {24971}
(1 row)

create or replace function recursive_get_deps(IN tbl oid, OUT oid oid, OUT relkind "char", OUT nspname name, OUT relname name, OUT deps oid[], OUT ori_oid oid, OUT ori_relkind "char", OUT ori_nspname name, OUT ori_relname name ) returns setof record as
$$
declare
begin
return query 
with recursive a as (
  select * from (
    select t1.oid,t1.relkind,t2.nspname,t1.relname,get_dep_oids(t1.oid) deps,(select t1.oid from pg_class t1,pg_namespace t2 where t1.relnamespace=t2.oid and t1.oid=tbl) as ori_oid from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and t1.relkind in ('m','v')
  ) t where t.ori_oid = any(t.deps)
union 
  select * from (
    select t1.oid,t1.relkind,t2.nspname,t1.relname,get_dep_oids(t1.oid) deps, a.oid as ori_oid from pg_class t1,pg_namespace t2,a where t1.relnamespace=t2.oid and t1.relkind in ('m','v')
  ) t where t.ori_oid = any(t.deps)
)
select a.oid,a.relkind,a.nspname,a.relname,a.deps,a.ori_oid,b.relkind ori_relkind, c.nspname ori_nspname,b.relname ori_relname from a,pg_class b,pg_namespace c where a.ori_oid=b.oid and b.relnamespace=c.oid order by a.nspname,a.relkind,a.relname;
end;
$$ language plpgsql strict;

=# select * from recursive_get_deps('pglog'::regclass);
  oid  │ relkind │ nspname │ relname │  deps   │ ori_oid │ ori_relkind │ ori_nspname │ ori_relname 
───────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────────┼─────────────┼─────────────
 24975 │ v       │ public  │ v1      │ {24971} │   24971 │ f           │ public      │ pglog
 24982 │ v       │ public  │ v2      │ {24975} │   24975 │ v           │ public      │ v1
 24987 │ v       │ sm1     │ v1      │ {24971} │   24971 │ f           │ public      │ pglog
(3 rows)

可以看到依赖到pglog表的有3个视图，分别是public schema下的 v1 和 v2 视图、sm1 schema下的v1 视图。

14:41:21 db: postgres@postgres, pid:54661
=# select * from pg_get_viewdef('v1',false);
          pg_get_viewdef           
───────────────────────────────────
  SELECT pglog.log_time,          ↵
     pglog.user_name,             ↵
     pglog.database_name,         ↵
     pglog.process_id,            ↵
     pglog.connection_from,       ↵
     pglog.session_id,            ↵
     pglog.session_line_num,      ↵
     pglog.command_tag,           ↵
     pglog.session_start_time,    ↵
     pglog.virtual_transaction_id,↵
     pglog.transaction_id,        ↵
     pglog.error_severity,        ↵
     pglog.sql_state_code,        ↵
     pglog.message,               ↵
     pglog.detail,                ↵
     pglog.hint,                  ↵
     pglog.internal_query,        ↵
     pglog.internal_query_pos,    ↵
     pglog.context,               ↵
     pglog.query,                 ↵
     pglog.query_pos,             ↵
     pglog.location,              ↵
     pglog.application_name       ↵
    FROM pglog;
(1 row)

=# select * from pg_get_viewdef('v2',false);
         pg_get_viewdef         
────────────────────────────────
  SELECT v1.log_time,          ↵
     v1.user_name,             ↵
     v1.database_name,         ↵
     v1.process_id,            ↵
     v1.connection_from,       ↵
     v1.session_id,            ↵
     v1.session_line_num,      ↵
     v1.command_tag,           ↵
     v1.session_start_time,    ↵
     v1.virtual_transaction_id,↵
     v1.transaction_id,        ↵
     v1.error_severity,        ↵
     v1.sql_state_code,        ↵
     v1.message,               ↵
     v1.detail,                ↵
     v1.hint,                  ↵
     v1.internal_query,        ↵
     v1.internal_query_pos,    ↵
     v1.context,               ↵
     v1.query,                 ↵
     v1.query_pos,             ↵
     v1.location,              ↵
     v1.application_name       ↵
    FROM v1;
(1 row)

=# select * from pg_get_viewdef('sm1.v1',false);
          pg_get_viewdef           
───────────────────────────────────
  SELECT pglog.log_time,          ↵
     pglog.user_name,             ↵
     pglog.database_name,         ↵
     pglog.process_id,            ↵
     pglog.connection_from,       ↵
     pglog.session_id,            ↵
     pglog.session_line_num,      ↵
     pglog.command_tag,           ↵
     pglog.session_start_time,    ↵
     pglog.virtual_transaction_id,↵
     pglog.transaction_id,        ↵
     pglog.error_severity,        ↵
     pglog.sql_state_code,        ↵
     pglog.message,               ↵
     pglog.detail,                ↵
     pglog.hint,                  ↵
     pglog.internal_query,        ↵
     pglog.internal_query_pos,    ↵
     pglog.context,               ↵
     pglog.query,                 ↵
     pglog.query_pos,             ↵
     pglog.location,              ↵
     pglog.application_name       ↵
    FROM pglog                    ↵
  LIMIT 10;
(1 row)