PostgreSQL-视图-03-查询对象依赖关系视图-dba_dependencies

PostgreSQL查询对象依赖关系视图

-- PostgreSQL查询对象依赖关系视图drop view if exists tzq.dba_dependencies;
create view tzq.dba_dependencies as 
with source_obj as (select sp.oid,sp.proname,unnest(string_to_array(regexp_replace(regexp_replace(lower(sp.prosrc),'--.*?\n'::text, ''::text, 'g'::text),'\W+'::text, ' '::text, 'g'::text), ' '::text))as split_text,sp.proowner::regrole::character varying as schema_name,sp.proownerfrom pg_proc spwhere (sp.proowner in (select pg_user.usesysid from pg_userwhere pg_user.usename ~~ 'tzq%'::text or pg_user.usename = 'jdbc_etl_in'::name or pg_user.usename = 'ewf'::name))
)
,all_depend as (select t.schema_name as owner,t.proname as name,'procedure'::text as type,p.proowner::regrole::character varying as referenced_owner,p.proname as referenced_name,'procedure'::text as referenced_type,p.proownerfrom source_obj tjoin pg_proc pon t.split_text = p.pronameand p.proowner <> 10::oidunion allselect t.schema_name as owner,t.proname as name,'procedure'::text as type,p.schemaname as referenced_owner,p.tablename as referenced_name,'table'::text as referenced_type,t.proownerfrom source_obj tjoin pg_tables pon t.split_text = p.tablenameand (p.schemaname ~~ 'tzq%'::text or p.schemaname = 'jdbc_etl_in'::name or p.schemaname = 'ewf'::name)
)
select distinctowner,name,type,referenced_owner,referenced_name,referenced_typefrom all_depend
;