Try pg_get_indexdef
, like so:
SELECT pg_get_indexdef(att.attrelid, att.attnum, true)
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid
JOIN pg_catalog.pg_attribute att ON att.attrelid = c.oid
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_am a ON a.oid = c.relam
CROSS JOIN LATERAL (
SELECT ARRAY (SELECT opc.opcname
FROM unnest(i.indclass::oid[]) WITH ORDINALITY o(oid, ord)
JOIN pg_opclass opc ON opc.oid = o.oid
ORDER BY o.ord)
) opc(operator_classes)
WHERE n.nspname !~ '^pg_'
AND c.relkind = ANY (ARRAY['r', 't', 'i']);
pg_get_indexdef |
"substring"(content, 1, 5) |
abs(file_id) |
child_id |
content_tsv |
content_tsv |
content_tsv |
father_id |
file_id |
file_id |
installed_rank |
round(sequence_no::double precision) |
round(sequence_no::double precision) |
success |
One should recall that an index can refer to multiple columns, and so can a function require multiple columns as arguments.
That said, by retrieving pg_get_indexdef
and applying a little regexp, we can get our arguments
SELECT (regexp_matches(
regexp_replace(
regexp_replace(pg_get_indexdef(att.attrelid, att.attnum, true),
att.attname,
'',
'g'),
'^\w|^,|^\:|"',
'',
'g'),
'\((.+)\)'))[1] AS fn_args
, pg_get_indexdef(att.attrelid, att.attnum, true)
, i.indrelid::regclass::text AS table
, c.relname AS index_name
, a.amname AS index_type
, opc.operator_classes
, i.indisunique AS is_unique
, att.attname as column_names
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid
JOIN pg_catalog.pg_attribute att ON att.attrelid = c.oid
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_am a ON a.oid = c.relam
CROSS JOIN LATERAL (
SELECT ARRAY (SELECT opc.opcname
FROM unnest(i.indclass::oid[]) WITH ORDINALITY o(oid, ord)
JOIN pg_opclass opc ON opc.oid = o.oid
ORDER BY o.ord)
) opc(operator_classes)
WHERE n.nspname !~ '^pg_'
AND c.relkind = ANY (ARRAY['r', 't', 'i'])
group by
i.indrelid, c.relname, a.amname, opc.operator_classes, i.indisunique, att.attrelid, att.attnum
Where only indices defined with function return rows here:
fn_args |
pg_get_indexdef |
table |
index_name |
index_type |
operator_classes |
is_unique |
column_names |
content, 1, 5 |
"substring"(content, 1, 5) |
file_lookup_4k |
date5_index |
btree |
{text_ops} |
0 |
substring |
file_id |
abs(file_id) |
file_lookup_4k |
date2_index |
btree |
{int4_ops} |
0 |
abs |
sequence_no::double precision |
round(sequence_no::double precision) |
file_lookup_4k |
date3_index |
btree |
{float8_ops} |
0 |
round |
sequence_no::double precision |
round(sequence_no::double precision) |
file_lookup_4k |
date4_index |
btree |
{float8_ops} |
0 |
round |