Re: array indizes in SQL

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