Re: Array in nested query

On Sat, Feb 14, 2009 at 05:36:11PM -0500, Ken Winter wrote:
> I'm trying to look up the columns in a constraint in pg_catalog (of
> PostgreSQL 8.0.x). I can't figure out how to "join" the elements of the
> array that lists the `attnum's of the columns in the table to the `conkey'
> array in the constraint definition (see
> http://www.postgresql.org/docs/8.0/static/catalog-pg-constraint.html and
> http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html ).
>
> The problem is in the last line of this query:
>
> SELECT a.attname AS name
>
> FROM pg_catalog.pg_class t, pg_catalog.pg_attribute a
>
> WHERE t.oid = 3626912
>
> AND a.attrelid = t.oid
>
> AND a.attnum = any (select conkey FROM pg_catalog.pg_constraint WHERE
> oid = 3708025)

SELECT a.attname AS name
FROM pg_catalog.pg_class t, pg_catalog.pg_attribute a
WHERE t.oid = 3626912
AND a.attrelid = t.oid
AND a.attnum IN (SELECT conkey FROM pg_catalog.pg_constraint WHERE oid = 3708025)

> I have tried all the variations on this syntax that I can think of, after
> plowing through all the documentation of arrays I can find in
> http://www.postgresql.org/docs/8.0/static/index.html, and none of them
> work.
>
> Any ideas?
>
> ~ TIA
>
> ~ Ken

Regards,
Gerhard