有些时候,我们需要查询哪些视图引用了某张数据表,在greenplumdb里面,可以创建如下的函数来查询,代码如下:
CREATE OR REPLACE FUNCTION public.get_views_used_by_table(text) RETURNS setof record as $FBODY$ select distinct vn.nspname || '.'||vc.relname as viewname from pg_class c join pg_namespace n on n.oid=c.relnamespace left join pg_depend d on d.refobjid =c.oid left join pg_rewrite r on r.oid =d.objid left join pg_class vc on r.ev_class =vc.oid left join pg_namespace vn on vc.relnamespace=vn.oid where d.deptype='n' and d.classid =2618 and r.rulename ='_RETURN' and vc.relkind='v' and c.oid =$1::regclass ; $FBODY$ LANGUAGE sql volatile;
使用如下:
testdb1=# select public.get_views_used_by_table('public.test9'); get_views_used_by_table ------------------------- (public.v_test9) (1 row) testdb1=# select * from public.get_views_used_by_table('public.test9') as A(viewname text); viewname ---------------- public.v_test9 (1 row)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步