PostgreSQL查询索引及字段详情（终极版）

-- PostgreSQL查询索引及字段详情（终极版）
with idx as (
select tc.relname as tablename  -- 表名,c.relname indexname      -- 对象名--,i.indkey--,array_length(i.indkey, 1) as array_length-- ,i.indkey[0] as field1,attr1.attname as field1-- ,i.indkey[1] as field2,attr2.attname as field2--,i.indkey[2] as field3,attr3.attname as field3--,i.indkey[3] as field4,attr4.attname as field4--,i.indkey[4] as field5,attr5.attname as field5--,i.indkey[5] as field6,attr6.attname as field6--,i.indkey[6] as field7,attr7.attname as field7-- ,i.* from pg_index ileft join pg_class con i.indexrelid = c.oidleft join pg_class tcon i.indrelid = tc.oidleft join pg_attribute attr1on attr1.attrelid = tc.oidand attr1.attnum = i.indkey[0]left join pg_attribute attr2on attr2.attrelid = tc.oidand attr2.attnum = i.indkey[1]left join pg_attribute attr3on attr3.attrelid = tc.oidand attr3.attnum = i.indkey[2]left join pg_attribute attr4on attr4.attrelid = tc.oidand attr4.attnum = i.indkey[3]left join pg_attribute attr5on attr5.attrelid = tc.oidand attr5.attnum = i.indkey[4]left join pg_attribute attr6on attr6.attrelid = tc.oidand attr6.attnum = i.indkey[5]left join pg_attribute attr7on attr7.attrelid = tc.oidand attr7.attnum = i.indkey[6]where tc.relname not like '%act\_%'and not tc.relname ~ '[0-9]{4}$'  -- 表名结尾不是4位数字的（不是备份表）and tc.relname not like '%\_other'and tc.relname not like '%\_others'and (tc.relname like '%\_t' or tc.relname like '%\_ti' or tc.relname like '%\_tmp')and tc.relowner = (select usesysid from pg_user where usename = CURRENT_SCHEMA)and indisunique = 't'  -- 唯一索引, 't' - 唯一索引；'f' - 普通索引。and indisprimary = 'f' -- 索引是唯一一个字段，是否是表的主键and c.relname not like '%\_pkey'
order by tablename,indexname
)
,idxs as (
select * from pg_indexeswhere schemaname = CURRENT_SCHEMAand indexdef like '%UNIQUE%'and tablename not like '%act\_%'and indexname not like '%\_pkey'
)
select idx.tablename,idx.indexname,idx.field1,idx.field2,idx.field3,idx.field4,idx.field5,idx.field6,idx.field7,case when position(' WHERE ' in idxs.indexdef)>0 then replace(replace(replace(substring(idxs.indexdef from (position(' WHERE ' in idxs.indexdef)+7) for length(idxs.indexdef)),'(',''),')',''),'::text','')else nullend as where_condition,idxs.indexdeffrom idxleft join idxson idx.indexname = idxs.indexname
;