Обсуждение: 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!

> -----Original Message-----
> From: Osvaldo Kussama [mailto:osvaldo.kussama@gmail.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@sunward.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