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

"Philip Hallstrom" <phallstrom(at)gmail(dot)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