Re: Array in nested query

Thanks, Osvaldo and Fernando - your solution works!

> -----Original Message-----
> From: Osvaldo Kussama [mailto:osvaldo(dot)kussama(at)gmail(dot)com]
> Sent: Saturday, February 14, 2009 8:24 PM
> To: Ken Winter
> Subject: Re: [GENERAL] Array in nested query
>
> 2009/2/14 Ken Winter <ken(at)sunward(dot)org>:
> > 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)
> >
> >
> >
> > 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.
> >
>
>
> I've a similar problem.
> Try explicit cast and an extra parenthesis:
>
> 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)::smallint[]);
>
> Osvaldo