Re: array indizes in SQL

On 11/12/07, Hans-Peter Oeri <hp(at)oeri(dot)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