Re: array indizes in SQL

On 11/12/07, Hans-Peter Oeri <hp@oeri.ch> wrote:

...

> 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

...

> Is there a better way to "decompose" an array? (that I didn't find)

SELECT a.attname AS "primarykey"
FROM pg_catalog.pg_attribute AS a,
     pg_catalog.pg_constraint AS o
WHERE a.attnum = ANY (o.conkey)
AND   a.attrelid = o.conrelid
AND   o.contype = 'p'
AND   o.conrelid = CAST ( ? AS regclass)
ORDER BY a.attnum ASC