校验全库缺失某列索引的全部表列表-PG版本

性能优化时，我们有可能会发现某个库某个表的必备索引缺失，而这个索引还是全库全相关表都必须包含的一个，那么我们就需要一个方法来检索库中其他表是否缺失该索引。PG版本解决方法如下

 

--创建函数
CREATE OR REPLACE FUNCTION public.queryLostColIndex(dbname VARCHAR(300), schema_prefix VARCHAR(300), col_name VARCHAR(300))
  RETURNS pg_catalog.void AS $BODY$
DECLARE
  tmpSql VARCHAR(900);
BEGIN
  --1.创建临时表
  DROP TABLE IF EXISTS public.tmp_lost_col_index;
  CREATE TABLE public.tmp_lost_col_index(
    schemaname VARCHAR(300),
    tablename VARCHAR(300));
  --2.插入临时表数据(符合库名、模式名条件，并且表中不存在指定字段索引的记录--只检索表中包含该字段的表)
  tmpSql := 'insert into public.tmp_lost_col_index ' ||
    'select allTable.schemaname, allTable.tablename from ' ||
    '(select schemaname, tablename from pg_tables tbs ' ||
    'where exists (select 1 from information_schema.columns cols where cols.column_name = ''' || col_name || ''' ' ||
    'and cols.table_catalog = ''' || dbname || ''' and cols.table_schema like ''' || schema_prefix || '%'' and tbs.schemaname = cols.table_schema and tbs.tablename = cols.table_name) ' ||
    ') allTable ' ||
    'left join (select schemaname, tablename from pg_indexes where indexdef like ''%' || col_name || '%'') idx ' ||
    'on allTable.schemaname = idx.schemaname and allTable.tablename = idx.tablename ' ||
    'where idx.tablename is null ';
  RAISE NOTICE 'notice: %', tmpSql;
  EXECUTE tmpSql;
  RAISE NOTICE 'job done!!!';
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
;

ALTER FUNCTION public.queryLostColIndex(dbname VARCHAR(300), schema_prefix VARCHAR(300), col_name VARCHAR(300)) OWNER TO artbase;



--执行函数
select public.queryLostColIndex('fb09', 'db_', 'n_ajbs');

--查看结果
select * from public.tmp_lost_col_index;

--删除临时表
drop table public.tmp_lost_col_index;