array indizes in SQL

Поиск
Список
Период
Сортировка
От Hans-Peter Oeri
Тема array indizes in SQL
Дата
Msg-id 47380011.1050608@oeri.ch
обсуждение исходный текст
Ответы Re: array indizes in SQL  ("Rodrigo De León" <rdeleonp@gmail.com>)
Re: array indizes in SQL  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-novice по дате отправления:

Предыдущее
От: Hans-Peter Oeri
Дата:
Сообщение: Re: design of queries for sparse data
Следующее
От: "Rodrigo De León"
Дата:
Сообщение: Re: array indizes in SQL