Нормализуйте индексы массива для одномерного массива, чтобы они начинались с 1

Question

PostgreSQL может работать с индексами массивов, начиная с любого места.
Рассмотрим пример, который создает массив из 3 элементов с индексами от 5 до 7:

SELECT ('[5:7]={1,2,3}'::int[]);

Возвращает:

[5:7]={1,2,3}

Это означает, например, что вы получите первый элемент с

SELECT ('[5:7]={1,2,3}'::int[])[5];

Я хочу нормализовать любой данный одномерный массив, чтобы начать с индекса 1.
Лучшее, что я мог придумать:

SELECT ('[5:7]={1,2,3}'::int[])[array_lower('[5:7]={1,2,3}'::int[], 1):array_upper('[5:7]={1,2,3}'::int[], 1)]

Или, то же самое, легче читать:

WITH x(a) AS (
    SELECT '[5:7]={1,2,3}'::int[]
    )
SELECT a[array_lower(a, 1):array_upper(a, 1)]
FROM   x

Вы знаете более простой / быстрый или хотя бы более элегантный способ?

---

эталонный тест

С целью тестирования производительности я подвел этот быстрый тест.
Таблица из 100 тыс. Строк, простой целочисленный массив произвольной длины от 1 до 11:

CREATE TEMP TABLE t (a int[]);
INSERT INTO t -- now with actually varying subscripts
SELECT ('[' || g%10 || ':' || 2*(g%10) || ']={1'
            || repeat(','||g::text, g%10) || '}')::int[]
FROM   generate_series(1,100000) g;

EXPLAIN ANALYZE
SELECT 
       substring(a::text, '{.*$')::int[]       -- Total runtime: 949.304 ms
--     a[-2147483648:2147483647]               -- Total runtime: 283.877 ms
--     a[array_lower(a, 1):array_upper(a, 1)]  -- Total runtime: 311.545 ms
FROM   t

Так что да, идея @ Дэниела немного быстрее.
Преобразование текста @ Кевина тоже работает, но не зарабатывает много очков.

Есть другие идеи?

Answer 1

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

Пример:

select ('[5:7]={1,2,3}'::int[])[-2147483648:2147483647];

результаты в:

  int4   
---------
 {1,2,3}

Answer 2

В конце концов, что-то более элегантное появилось в Postgres 9.6.
Руководство:

Можно опустить lower-bound и / или upper-bound спецификатора среза; отсутствующая граница заменяется нижним или верхним пределом индексов массива. Например:

Теперь все просто:

SELECT ('[5:7]={1,2,3}'::int[])[:];

Примерно такая же производительность, что и у решения Daniel с жестко закодированными подписчиками max-массивов, что по-прежнему является подходом для Postgres 9.5 или более ранних версий.

Answer 3

Не уверен, что это уже покрыто, но:

SELECT array_agg(v) FROM unnest('[5:7]={1,2,3}'::int[]) AS a(v);

Чтобы проверить производительность я должен был добавить id столбец на тестовой таблице. Медленный.