Thanks, Osvaldo and Fernando - your solution works! > -----Original Message----- > From: Osvaldo Kussama [mailto:osvaldo.kussama@xxxxxxxxx] > Sent: Saturday, February 14, 2009 8:24 PM > To: Ken Winter > Subject: Re: Array in nested query > > 2009/2/14 Ken Winter <ken@xxxxxxxxxxx>: > > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
