From: | darcy(at)druid(dot)net (D'Arcy J(dot)M(dot) Cain) |
---|---|
To: | vadim(at)krs(dot)ru (Vadim Mikheev) |
Cc: | hackers(at)postgresql(dot)org, pgsql-core(at)postgresql(dot)org |
Subject: | Re: [HACKERS] Adding PRIMARY KEY info |
Date: | 1998-09-05 10:32:04 |
Message-ID: | m0zFFdU-00006JC@druid.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at){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.
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-09-05 14:20:39 | Re: [HACKERS] MIN not defined for OID types |
Previous Message | Vadim Mikheev | 1998-09-05 05:40:43 | Re: [HACKERS] Adding PRIMARY KEY info |