Skip to main content
added 6 characters in body
Source Link

Ultimately, you are getting the function name back because that function call is being used to build the index, and not simply the underlying column.

Look at the pg_catalog.pg_index.indexprs column in your query and you will see something like the following, which exposes how the function is indexed:

({FUNCEXPR :funcid 1342 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 
:args ({FUNCEXPR :funcid 316 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 2 :funccollid 0 :inputcollid 0 
:args ({VAR :varno 1 :varattno 2 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 50}) :location -1}) :location 44})

Ultimately, you are getting the function name back because that function call is being used to build the index, and not simply the underlying column.

Look at the pg_catalog.pg_index.indexprs column in your query and you will see something like the following, which exposes how the function is indexed:

({FUNCEXPR :funcid 1342 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 
:args ({FUNCEXPR :funcid 316 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 2 :funccollid 0 :inputcollid 0 
:args ({VAR :varno 1 :varattno 2 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 50}) :location -1}) :location 44})
added 6 characters in body
Source Link

Where only indices defined with function calls return rows here:

Where only indices defined with function return rows here:

Where only indices defined with function calls return rows here:

Source Link

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