The PostgreSQL adapter of the Ruby on Rails database connector contains
the following statement to find all indexes on a table, including the
indexed columns.

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 = '#{table_name}'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN
(#{schemas}) )
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

Admittedly, I haven't even tried to understand what's going on here.
However, I've looked at the code of pgadmin3 which is no less involved.

Anyway, the effect I've noticed is that the statement above does find
all the columns of each index, but it returns them in an arbitrary
order. And indeed, there's nothing obviously imposing an ordering on
a.attname.

Is there a fix for this, or is the statement beyond repair? I've looked
at the information schema, as implemented in PostgreSQL 8.3, and
apparently it is not possible to gather information on indexes from it.

Michael

--
Michael Schuerig
mailto:michael (AT) schuerig (DOT) de
http://www.schuerig.de/michael/

Michael Schuerig wrote:
The statement above will also not find functional indexes.

But it's easy to fix it so that the columns come in the order of
the index definition:

SELECT t.oid, i.relname, d.indisunique, a.attname
FROM pg_class t, pg_class i, pg_index d, pg_attribute a,
generate_series(0, 9) AS c(i)
WHERE i.relkind = 'i'
AND d.indexrelid = i.oid
AND d.indisprimary = 'f'
AND t.oid = d.indrelid
AND t.relname = '#{table_name}'
AND i.relnamespace IN
(SELECT oid FROM pg_namespace WHERE nspname IN (#{schemas}) )
AND a.attrelid = t.oid
AND d.indkey[c.i]=a.attnum
ORDER BY i.relname, c.i

Yours,
Laurenz Albe

Laurenz Albe wrote:


Thanks a lot! Easy is relative, I reckon.

Michael

--
Michael Schuerig
mailto:michael (AT) schuerig (DOT) de
http://www.schuerig.de/michael/