Re: array indizes in SQL

Hans-Peter Oeri <hp(at)oeri(dot)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