Как получить доступ к внутреннему индексу массива с помощью postgreSQL?

Это мое (возможно, обычное для вас) неоптимизированное решение:

Обходной путь для проблемы PG с неоптимизированной внутренней функцией:

CREATE FUNCTION unnest_with_idx(anyarray)
RETURNS TABLE(idx integer, val anyelement) AS
$$ 
   SELECT generate_series(1,array_upper($1,1)) as idx, unnest($1) as val;
$$ LANGUAGE SQL IMMUTABLE;

Тестовое задание:

SELECT idx,val from unnest_with_idx(array[1,20,3,5]) as t;

Но, как я уже сказал, не оптимизирован. Я не могу поверить (!!), что PostgreSQL не имеет внутреннего индекса для массивов...? Но в этом случае вопрос заключается в том, как напрямую получить доступ к этому индексу, где находится GIN-подобный внутренний счетчик?

ПРИМЕЧАНИЕ 1. Решение, приведенное выше, и вопрос не совпадают с вопросом: " Как создать индекс по каждому элементу массива?". Также не то же самое, что " Может ли столбец индексного массива PostgreSQL?", Потому что функция предназначена для изолированного массива, а не для индекса таблицы для полей массива.

ПРИМЕЧАНИЕ 2 (отредактировано после ответов): "индексы массива" (более популярный термин) или "индексы массива" или "счетчик массива" - это термины, которые мы можем использовать в семантическом пути для ссылки "внутреннего счетчика", аккумулятора на следующий массив вещь. Я вижу, что ни одна команда PostgreSQL не предлагает прямой доступ к этому счетчику. Как generate_series() функция, generate_subscripts() Функция является генератором последовательности, и производительность (лучше, но) почти одинакова. С другой стороны row_number() Функция предлагает прямой доступ к "внутреннему счетчику строк", но речь идет о строках, а не о массивах, и, к сожалению, производительность хуже.

Источник

2 ответа

Решение

PostgreSQL предоставляет специальные функции для генерации подписок массива:

WITH   x(a) AS ( VALUES ('{1,20,3,5}'::int[]) )
SELECT generate_subscripts(a, 1) AS idx
      ,unnest(a) AS val
FROM   x;

Фактически он выполняет почти то же самое, что и запрос @Frank, только без подзапроса.
Плюс это работает с подписчиками, которые не начинаются с 1,

Любое решение работает только для одномерных массивов! (Может быть легко расширен до нескольких измерений.)

Функция:

CREATE OR REPLACE FUNCTION unnest_with_idx(anyarray) 
RETURNS TABLE(idx integer, val anyelement) LANGUAGE SQL IMMUTABLE AS
$func$
  SELECT generate_subscripts($1, 1), unnest($1);
$func$;

Вызов:

SELECT * FROM unnest_with_idx('{1,20,3,5}'::int[]);

Также учтите:

SELECT * FROM unnest_with_idx('[4:7]={1,20,3,5}'::int[]);

Подробнее о массивах подписчиков в этом связанном вопросе.

Если вы действительно хотите нормализовать подписки (начиная с 1), я бы использовал:

SELECT generate_series(1, array_length($1,1)) ...

Это почти тот вопрос, который у вас уже был, только с array_length() вместо array_upper() - который потерпит неудачу с нестандартными подписками.

Спектакль

Я провел быстрый тест на массиве 1000 int со всеми представленными здесь запросами. Все они работают примерно одинаково (~ 3,5 мс) - за исключением row_number() на подзапрос (~ 7,5 мс) - как и ожидалось, из-за подзапроса.

Обновление: Postgres 9.4+

Если вы не работаете с нестандартными индексными подписками, используйте новый WITH ORDINALITY вместо:

Источник

row_number() работает:

SELECT 
    row_number() over(), 
    value
FROM (SELECT unnest(array[1,20,3,5])) a(value);

Тогда оптимизированная функция будет

CREATE OR REPLACE FUNCTION unnest_with_idx(anyarray) 
RETURNS table(idx integer, val anyelement) AS $$ 
  SELECT (row_number() over())::integer as idx, val
  FROM (SELECT unnest($1)) a(val);
$$ LANGUAGE SQL IMMUTABLE;
Источник
Другие вопросы по тегам