Thread: Array in nested query
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.
Any ideas?
~ TIA
~ Ken
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
Attachment
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