Thread: Help with query to return indexes (including functional ones!) on a given table

Hi all -

I'm trying to add functional index support to Rails' Active Record and
am getting stuck when it comes to a method Rails has to print out the
indexes associated with a given table.

The SQL being run is below:

SELECT distinct i.relname, d.indisunique, a.attname
FROM pg_class t, pg_class i, pg_index d, pg_attribute a
WHERE i.relkind = 'i'
AND d.indexrelid = i.oid
AND d.indisprimary = 'f'
AND t.oid = d.indrelid
AND t.relname = 'employers'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN
('public') )
AND a.attrelid = t.oid
AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum
OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum
OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum
OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum
OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )
ORDER BY i.relname;

This returns the following:

         relname                        | indisunique | attname
--------------------------------------+-------------+---------
 foo_idx                                  | f           | name
 foo_idx                                  | f           | url
 index_employers_on_name | f           | name

But that doesn't show a functional index I created on the employers
table.  This does:

careers_development=# select indexname, indexdef from pg_indexes where
tablename = 'employers';
 indexname        |                                     indexdef
-------------------------+-----------------------------------------------------------------------------------
 employers_pkey                    | CREATE UNIQUE INDEX
employers_pkey ON employers USING btree (id)
 index_employers_on_name  | CREATE INDEX index_employers_on_name ON
employers USING btree (name)
 index_employers_on_url       | CREATE INDEX index_employers_on_url ON
employers USING btree (lower((url)::text))
 foo_idx                                   | CREATE INDEX foo_idx ON
employers USING btree (name, url)

I don't know enough about PG's internals to quite know what I need to
query on, but what I'd like is to have a query that returns the first
result set with the following addition:

         relname                        | indisunique | attname
--------------------------------------+-------------+---------
index_employers_on_url        f                 lower(url)


Is there anyway to do that beyond parsing the CREATE INDEX string?
Some way to modify that first query to include the functional index
and somehow get the function part of it into that last column?

Appreciate any pointers any of you might have on this.

Thanks!

"Philip Hallstrom" <phallstrom@gmail.com> writes:
> I'm trying to add functional index support to Rails' Active Record and
> am getting stuck when it comes to a method Rails has to print out the
> indexes associated with a given table.

> The SQL being run is below:

> SELECT distinct i.relname, d.indisunique, a.attname
> FROM pg_class t, pg_class i, pg_index d, pg_attribute a
> WHERE i.relkind = 'i'
> AND d.indexrelid = i.oid
> AND d.indisprimary = 'f'
> AND t.oid = d.indrelid
> AND t.relname = 'employers'
> AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN
> ('public') )
> AND a.attrelid = t.oid
> AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum
> OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum
> OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum
> OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum
> OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )
> ORDER BY i.relname;

Well, the problem with this is that it only considers simple index keys,
ie, not indexed expressions.  The multi-argument version of
pg_get_indexdef() would probably help.  Something like

SELECT i.relname, d.indisunique, pg_get_indexdef(i.oid, a.attnum, false)
FROM pg_class t, pg_class i, pg_index d, pg_attribute a
WHERE i.relkind = 'i'
AND d.indexrelid = i.oid
AND d.indisprimary = 'f'
AND t.oid = d.indrelid
AND t.relname = 'employers'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN
('public') )
AND a.attrelid = i.oid
ORDER BY i.relname;

Note I've flipped the meaning of the "a" table to be attributes of the
index not of the table; this is a handy way to get all the attnum
values (index column numbers) we need to pass to pg_get_indexdef().

BTW, why are you suppressing primary keys?

            regards, tom lane