Re: unique indexes

Tom,

Thanks for the update on this query.  I'm not positive where I found this
query, but I'm pretty sure it was for a v6.5x something.  Anyway, thanks.
phpPgAdmin has been updated.

-Dan

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Dan Wilson" <phpPgAdmin@acucore.com>
Cc: <jason@netspade.com>; <pgsql-general@postgresql.org>
Sent: Sunday, November 19, 2000 10:14 AM
Subject: Re: [GENERAL] 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