[NOVICE] array indizes in SQL

From: Hans-Peter Oeri <hp(at)oeri.ch>
Date: Mon Nov 12 2007 - 02:26:09 EST

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

---------------------------(end of broadcast)---------------------------

TIP 5: don't forget to increase your free space map settings Received on Mon Nov 12 02:26:35 2007