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
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 |