Thus spake Vadim Mikheev
> D'Arcy J.M. Cain wrote:
> > Nope, pg_index is fine by me. Now, once we have this, how do we find
> > the index for a particular attribute? I can't seem to figure out the
> > relationship between pg_attribute and pg_index. The chart in the docs
> > suggests that indkey is the relation but I can't see any useful info
> > there for joining the tables.
>
> pg_index:
> indrelid - oid of indexed relation
> indkey - up to the 8 attnums
>
> pg_attribute:
> attrelid - oid of relation
> attnum - ...
>
> Without outer join you have to query pg_attribute for each
> valid attnum from pg_index->indkey -:(
Hmmm. Well, to start with, perhaps I can specify that the functions
only work with simple keys. Do we even support complex primary keys?
Anyway, if I do that then the following should work with indisunique
replaced by indisprimary.
SELECT pg_class.relname, pg_attribute.attname
FROM pg_class, pg_attribute, pg_index
WHERE pg_class.oid = pg_attribute.attrelid AND
pg_class.oid = pg_index.indrelid AND
pg_index.indkey[0] = pg_attribute.attnum AND
pg_index.indisunique = 't';
In fact, the above would work if I could assume that each table had only
one unique index but I think that that's too much of a restriction. I
hope you can add that flag for this release.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.