如何检索postgres函数索引列名

Question

我想检索postgresdb中的所有函数索引及其列名。但是在绑定了很多之后，我无法获得函数索引列名。

从下面的查询中，我可以得到单个普通索引类型。

select  t.relname as tableName, 
i.relname as indexName, 
STRING_AGG(pga.attname||'', ','order by i.relname,pga.attnum)   as columnName             
from pg_class t 
inner join pg_index ix on t.oid = ix.indrelid 
inner join pg_class i on i.oid = ix.indexrelid 
inner join pg_attribute pga on pga.attrelid = i.oid 
inner join pg_indexes pgidx on pgidx.indexname=i.relname 
where t.relkind = 'r' 
and pgidx.schemaname = ?
group by t.relname, i.relname 
having count(*) = 1 
order by i.relname

Answer 1

在阅读这里的文档时，我创建了一个(小(测试：

luuk=# create table test1 (col1 varchar(20));
luuk=# insert into test1 values('test'),('Test'),('TEST');
luuk=# select * from test1 where lower(col1)='test';
col1
------
test
Test
TEST
(3 rows)
luuk=# CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

luuk=# select indexdef from pg_indexes where indexname='test1_lower_col1_idx';
indexdef
-------------------------------------------------------------------------------------
CREATE INDEX test1_lower_col1_idx ON public.test1 USING btree (lower((col1)::text))
(1 row)
luuk=#

我确实看到了创建的函数索引的完整定义。现在只需要解析这个字符串就可以找到所有的列名。