Re: database introspection error

Jon Nelson <jnelson+pgsql@jamponi.net> wrote:

> Would the following query be more (most?) correct, assuming the
> oid of the table is known?

It doesn't seem to work for indexes on expressions.

Try this:

create table t (id int not null primary key, txt text);
create index t_weird on t ((substring(txt, 1, id)));
create index t_txt_notnull on t (txt) where txt is not null;
SELECT
    i.relname as relname,
    ix.indisunique, ix.indexprs, ix.indpred,
    a.attname as column_name
  FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
  WHERE
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.oid = (select oid from pg_class where relname = 't')
  ORDER BY
    t.relname,
    i.relname
;

-Kevin