Hi!
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
As you see, I'm not really satisfied. Although a pk with more than five
fields surely is rare (and bad design), I would like my query to be
"clean" - even if some stranger would create a six field pk. (problem
applies not only to pks, of course)
Is there a way to adapt the series maximum to the actual array size - at
least get an upper bound?
Is there a better way to "decompose" an array? (that I didn't find)
Thanks
HPO