When an index is created from a function on a column, we get the function name instead of the column name in pg_attribute in Postgres. How to fix it?

Question

I have come up with the following query to get the table name, index_type, operator_classes, and uniqueness of indexes based on the answers mostly from @Erwin Brandstetter:

SELECT i.indrelid::regclass::text AS table
     , c.relname AS index_name
     , a.amname AS index_type
     , opc.operator_classes
     , i.indisunique AS is_unique
     , array_agg(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
ORDER  BY 1, 2, 3, 4;

This works great, except it returns the wrong column names when the index is created by transforming data from another column. For example, with the following index:

CREATE INDEX _ixtr_luxury_inventory_images ON public.luxury_inventory
 USING gin (f_textarr2text(images) gin_trgm_ops)

returns:

table            | luxury_inventory
index_name       | _ixtr_luxury_inventory_images
index_type       | gin
operator_classes | ['gin_trgm_ops']
is_unique        | False
column_names     | ['f_textarr2text']

We get f_textarr2text as the column name instead of images.

How can we get the correct column_names? I'm on Postgres 16.2 if that matters.

Update: Here is the table creation SQL:

create table "luxury_inventory" (
id bigserial primary key,
images text[])

CREATE OR REPLACE FUNCTION "public".f_textarr2text(text[]) 
RETURNS text LANGUAGE sql IMMUTABLE AS $$SELECT array_to_string($1, ',')$$;

Answer 1

Thinking on this for a moment, perhaps better than pg_get_indexdef is pulling column ordinals out of the indexprs column noted in the first answer, and specifically the varattno field.

regexp_matches over the 'indexprs' (converting pg_tree_node to text) for varattno)

I threw together the following, going back to the definition in the columns table of information_schema:

SELECT (SELECT JSON_AGG(columns.column_name)
          FROM information_schema.columns
         WHERE table_schema = 'public'
           AND table_name   = i.indrelid::regclass::text
           AND ordinal_position IN (SELECT matches[1]::INTEGER 
                                    FROM regexp_matches(i.indexprs::TEXT,
                                                        'varattno (\d)',
                                                        'g') as matches)) AS argument_columns
     , pg_get_indexdef(att.attrelid, att.attnum, true) 
     , i.indrelid::regclass::text AS table
     , c.relname AS index_nameq
     , 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
WHERE  n.nspname !~ '^pg_'
AND    c.relkind IN ('r', 't', 'i')

and that produces:

argument_columns	pg_get_indexdef	table	index_nameq	column_names
NULL	content_tsv	file_lookup_16k	fl_16k_tsv_idx	content_tsv
NULL	content_tsv	file_lookup_4k	fl_4k_tsv_idx	content_tsv
NULL	file_id	file_lookup_4k	date_index	file_id
["file_id"]	abs(file_id)	file_lookup_4k	date2_index	abs
["sequence_no"]	round(sequence_no::double precision)	file_lookup_4k	date3_index	round
["sequence_no"]	round(sequence_no::double precision)	file_lookup_4k	date4_index	round
["content"]	"substring"(content, 1, 5)	file_lookup_4k	date5_index	substring
["file_id", "sequence_no"]	abs(file_id - sequence_no)	file_lookup_4k	date6_index	abs
NULL	content_tsv	file_lookup_8k	fl_8k_tsv_idx	content_tsv

I feel that this is far more in line with what we would both want.

Note that:

• I joined only to the attname table, because that is really all I need
• JSON_AGG(columns.column_name) can be replaced with JSON_AGG(columns.*) or the like to select the entire row for the columns.
• ANY (ARRAY['r', 't', 'i'] was updated to IN ANY ('r', 't', 'i')

---

EDIT:

In order to bring this into a single column, Let's COALESCE, and whenever the new function returns NULL, we will insert an array containing the result of the pg_get_indexdef, and prefer ARRAY_AGG to JSON_AGG. All together:

SELECT COALESCE((SELECT ARRAY_AGG(columns.column_name)
                 FROM information_schema.columns
                WHERE table_schema = 'public'
                  AND table_name   = i.indrelid::regclass::text
                  AND ordinal_position IN (SELECT matches[1]::INTEGER 
                                           FROM regexp_matches(i.indexprs::TEXT,
                                                 'varattno (\d)',
                                                 'g') as matches)), 
                ARRAY[(pg_get_indexdef(att.attrelid, att.attnum, true))]) AS argument_columns
     , i.indrelid::regclass::text AS table
     , c.relname AS index_nameq
     , 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
WHERE  n.nspname !~ '^pg_'
AND    c.relkind IN ('r', 't', 'i');

This produces:

argument_columns	table	index_nameq	column_names
{file_id}	file_lookup_4k	date_index	file_id
{file_id}	file_lookup_4k	date2_index	abs
{sequence_no}	file_lookup_4k	date3_index	round
{sequence_no}	file_lookup_4k	date4_index	round
{content}	file_lookup_4k	date5_index	substring
{file_id,sequence_no}	file_lookup_4k	date6_index	abs

Answer 2

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 calls 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

---

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})