Array in nested query

What error are you getting?

I tried your query and I had to add an explicit cast to smallint[] to
make it work. Like this:

... a.attnum = any ((select conkey FROM pg_catalog.pg_constraint WHERE

oid = 3708025)::smallint[]);

It seems strange to me, I didn't expect the ANY clause to need that
cast. Or maybe I'm missing something.

Cheers.

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

2009/2/15 Fernando Moreno <azazel.7@gmail.com>:

What error are you getting?

I tried your query and I had to add an explicit cast to smallint[] to
make it work. Like this:

... a.attnum = any ((select conkey FROM pg_catalog.pg_constraint WHERE

oid = 3708025)::smallint[]);

It seems strange to me, I didn't expect the ANY clause to need that
cast. Or maybe I'm missing something.

Look this thread:
http://archives.postgresql.org/pgsql-hackers/2008-12/msg00496.php

Osvaldo

Thanks, Osvaldo and Fernando - your solution works!