Howdy:
Is there a way (SQL query) to get a list of
tables and their indexes (rather, what the
\di <index_name> brings back)?
I am doing the 'psql -U <name> -d <database> -E
to figure out how the description of \d <table>
give the 'Indexes:' information at the bottom of
the page, but I can't seem to figure out
how it's pulling that data back (aside from
querying against pg_class and pg_index and
why it is going there).
Just to make myself clear - I am not talking
about the following:
[snip]
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 't_table'
AND c.oid = i.indrelid AND i.indexrelid = c2.oid
AND NOT i.indisunique ORDER BY c2.relname
[/snip]
But something that I can put into a script to
to identify the table and bring back this:
[snip from \di <index_name>]
Index "t_table_2003_idx"
Column | Type
-----------+---------
person_id | integer
btree
[/snip from \di <index_name>}
Thanks in advance.
-X