函数脚本写得多了,再想统计整理用到的表信息十分麻烦。甚至存在历史数据表不再使用的情况,所以需要根据条件对函数引用表信息进行统计。本篇介绍我常用的脚本。
1. 查找某数据表在哪些函数中引用过
select proname from pg_proc where prosrc like '%rpt_user_info%';
2. 查找某数据表在某模式下的哪些函数中引用过
select proname from
pg_proc proc,
pg_namespace spaces
where
proc.pronamespace = spaces.oid
and spaces.nspname = 'anrpt'
and proc.prosrc like '%anrpt.rpt_user_info%';
3. 查询某模式下的所有表在某模式下的所有函数中的使用情况(通过函数实现)
3.1 建结果表
-- 函数引用表信息
DROP TABLE IF EXISTS anrpt.stat_table_info;
CREATE table anrpt.stat_table_info(
table_nspname varchar(20), --表模式
pro_nspname varchar(20), --函数模式
tablename varchar(50), --表名
proname varchar(200) --函数名
) DISTRIBUTED BY (proname)
3.2 编写函数
CREATE OR REPLACE FUNCTION "anrpt"."p_stat_table_info"(IN v_table_nspname varchar,IN v_pro_nspname varchar, OUT v_retcode varchar, OUT v_retinfo varchar)
RETURNS "pg_catalog"."record" AS $BODY$
/**
函数说明:根据传入的需要查询的表所在模式和函数模式,输出函数所引用模式下所有表名信息
调用方法:
SELECT anrpt.p_stat_table_info('dim','anrpt');
结果检查:
SELECT * FROM anrpt.stat_table_info where table_nspname = 'dim' and pro_nspname = 'anrpt' ORDER BY tablename ;
**/
DECLARE
v_sql text;
v_relname varchar(200);
BEGIN
delete from anrpt.stat_table_info where table_nspname = v_table_nspname and pro_nspname = v_pro_nspname;
for v_relname in SELECT aa.relname FROM pg_class aa LEFT JOIN pg_namespace dd ON dd.oid = aa.relnamespace WHERE dd.nspname = v_table_nspname loop
v_sql := '
insert into anrpt.stat_table_info
SELECT
'''||v_table_nspname||''',spaces.nspname,'''||v_relname||''',proc.proname
FROM
pg_proc proc,
pg_namespace spaces
WHERE
proc.pronamespace = spaces.oid
AND spaces.nspname = '''||v_pro_nspname||'''
AND proc.prosrc LIKE ''%'||v_table_nspname||'.'||v_relname||' %''
';
---- 注意百分号前有空格,防止相同表名前缀造成关联多余数据,如函数中使用dim_user_info,dim模式下同样有dim_user表也会被查到!
raise notice 'v_sql is %',v_sql;
EXECUTE v_sql ;
end loop;
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
大功告成!