Hans-Peter Oeri <hp@oeri.ch> writes: > I flee arrays in SQL whenever I can... however, to programmatically get > index fields from the system catalog, there I am. :( > E.g. to get the pk fields I wrote: > SELECT > a.attname AS "primarykey" > FROM > generate_series(1,5) as i, -- yuck!! > pg_catalog.pg_attribute as a, > pg_catalog.pg_constraint as o > WHERE > o.conkey[i] = a.attnum > AND a.attrelid = o.conrelid > AND o.contype = 'p' > AND o.conrelid = CAST( ? AS regclass ) > ORDER BY > i ASC [ sorry for not responding sooner ] Did you look into the information_schema views to see how this has been dealt with before? Those views rely on this set-returning function: /* Expand any 1-D array into a set with integers 1..N */ CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int) RETURNS SETOF RECORD LANGUAGE sql STRICT IMMUTABLE AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1 from pg_catalog.generate_series(pg_catalog.array_lower($1,1), pg_catalog.array_upper($1,1), 1) as g(s)'; Your problem would work out like this: SELECT a.attname AS "primarykey" FROM pg_catalog.pg_attribute as a, (SELECT conrelid, information_schema._pg_expandarray(conkey) as k FROM pg_catalog.pg_constraint as o WHERE o.contype = 'p' AND o.conrelid = CAST( 'foo' AS regclass ) ) ss WHERE a.attrelid = ss.conrelid AND a.attnum = (ss.k).x ORDER BY (ss.k).n ASC ; regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend