Re: [HACKERS] Adding PRIMARY KEY info

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.