PostgreSQL查詢對(duì)象依賴關(guān)系視圖文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-613655.html
-- PostgreSQL查詢對(duì)象依賴關(guān)系視圖
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.proowner
from pg_proc sp
where (sp.proowner in (select pg_user.usesysid
from pg_user
where 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.proowner
from source_obj t
join pg_proc p
on t.split_text = p.proname
and p.proowner <> 10::oid
union all
select 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.proowner
from source_obj t
join pg_tables p
on t.split_text = p.tablename
and (p.schemaname ~~ 'tzq%'::text or
p.schemaname = 'jdbc_etl_in'::name or
p.schemaname = 'ewf'::name)
)
select distinct
owner
,name
,type
,referenced_owner
,referenced_name
,referenced_type
from all_depend
;
文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-613655.html
到了這里，關(guān)于PostgreSQL-視圖-03-查詢對(duì)象依賴關(guān)系視圖-dba_dependencies的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
