Re: unique indexes

"Dan Wilson" <phpPgAdmin@acucore.com> writes:
> Here is the query from phpPgAdmin that does what you are asking for:

>   SELECT
>    ...
>    and
>    (
>     i.indkey[0] = a.attnum
>     or
>     i.indkey[1] = a.attnum
>     or
>     i.indkey[2] = a.attnum
>     or
>     i.indkey[3] = a.attnum
>     or
>     i.indkey[4] = a.attnum
>     or
>     i.indkey[5] = a.attnum
>     or
>     i.indkey[6] = a.attnum
>     or
>     i.indkey[7] = a.attnum
>    )
>    ...

> This was adapted from the psql source.  Hope it's what you need.

Actually I think it was borrowed from a very crufty query in the ODBC
driver.  Aside from being ugly, the above-quoted clause is now wrong,
because indexes can have more than 8 keys since 7.0.  This is how ODBC
finds matching keys and attributes now:

SELECT ta.attname, ia.attnum
FROM pg_attribute ta, pg_attribute ia, pg_class c, pg_index i
WHERE c.relname = '$indexname'
AND c.oid = i.indexrelid
AND ia.attrelid = i.indexrelid
AND ta.attrelid = i.indrelid
AND ta.attnum = i.indkey[ia.attnum-1]
ORDER BY ia.attnum

which is cleaner since it doesn't assume anything about the max
number of keys.

            regards, tom lane