array indizes in SQL

From: Hans-Peter Oeri <hp(at)oeri(dot)ch>
To: ListaPostgre <pgsql-novice(at)postgresql(dot)org>
Subject: array indizes in SQL
Date: 2007-11-12 07:26:09
Message-ID: 47380011.1050608@oeri.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Rodrigo De León 2007-11-12 13:48:58 Re: array indizes in SQL
Previous Message Hans-Peter Oeri 2007-11-12 07:11:09 Re: design of queries for sparse data