Поиск позиции значения в массивах PostgreSQL

Как я могу получить позицию значения в массивах PostgreSQL? Там .index() метод для Python и array_search() функция для PHP, но я не могу найти такую функцию для PostgreSQL. Должен ли я писать хранимую функцию для этого? Я предпочитаю решать с помощью встроенной функции.

Ответы

Ответ 1

Начиная с версии 9.5, встроенные функции: array_position() и array_positions(), для поиска ключа массива (только первое вхождение) или ключи (все вхождения), по значению.

Эти функции поддерживают anyarray-тип.

Ответ 2

Документация рекомендует с помощью функции generate_subscripts. Функция ниже подражает PHP array_search:

CREATE FUNCTION array_search(needle ANYELEMENT, haystack ANYARRAY)
RETURNS INT AS $$
    SELECT i
      FROM generate_subscripts($2, 1) AS i
     WHERE $2[i] = $1
  ORDER BY i
$$ LANGUAGE sql STABLE;

Возвращает индекс первого совпадения, если он присутствует. Если вы хотите все совпадения, просто измените RETURNS INT на RETURNS SETOF INT. Эта функция, как есть, возвращает NULL, если совпадение не найдено.

Эта функция работает только с одномерными массивами.

Также учтите, что array_search(NULL, a) всегда возвращает NULL, даже если массив содержит нулевые элементы:

> SELECT array_search(null, array[1, 2, null, 4]);
 array_search 
--------------

(1 row)

Это потому, что SQL считает, что NULL = NULL неизвестен (т.е. NULL). См. functions-comparison. Если вы хотите, чтобы array_search находил элементы NULL, измените

     WHERE $2[i] = $1

     WHERE $2[i] IS NOT DISTINCT FROM $1

Ответ 3

Для целых массивов вы можете использовать значительно более быструю idx из добавленного расширения intarray.

Эта функция еще не была обобщена для поддержки всех типов массивов, к сожалению, поэтому вы придерживаетесь очень медленного подхода SQL для других массивов.