Problem: concat an array of arrays

Поиск
Список
Период
Сортировка
От Belka Lambda
Тема Problem: concat an array of arrays
Дата
Msg-id 31681272502108@web73.yandex.ru
обсуждение исходный текст
Ответы Re: Problem: concat an array of arrays  (Merlin Moncure <mmoncure@gmail.com>)
Список pgsql-general
Hi!

I tried to write a recursive SELECT, that would do the concatination, but a problem appeared:
can't make a {1,2,3} from {{1,2,3}}.
Here are some experiments:
---------------------------------------------------------------------------------------------------
postgres=# select array[array[1,2,3],array[4,5,6],array[7,8,9],array[10,11,12]];

                array
--------------------------------------
 {{1,2,3},{4,5,6},{7,8,9},{10,11,12}}
(1 row)


postgres=# select (array[array[1,2,3],array[4,5,6],array[7,8,9],array[10,11,12]]
)[3];
 array
-------

(1 row)


postgres=# select (array[array[1,2,3],array[4,5,6],array[7,8,9],array[10,11,12]]
)[3:3];
   array
-----------
 {{7,8,9}}
(1 row)


postgres=# select (array[array[1,2,3],array[4,5,6],array[7,8,9],array[10,11,12]]
)[3][1];
 array
-------
     7
(1 row)
-------------------------------------------------------------------------------------------

The original query, that would do the concatenation:
---------------------------------------------------------------
WITH RECURSIVE unfold (rest, accum) AS (
        VALUES ($1 :: int[][], ARRAY[] :: int[])
      UNION ALL
        SELECT u.rest[2:array_length(u.rest, 1)] AS rest, array_cat(u.rest[1], u.accum) AS accum
        FROM unfold AS u
        WHERE array_length(u.rest, 1) > 0
)
SELECT u.accum
FROM unfold AS u
WHERE array_length(u.rest, 1) = 0;
---------------------------------------------------------------
Throws an error:
ERROR:  function array_cat(integer, integer[]) does not exist

What am I doing wrong?

Please help, Belka

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

Предыдущее
От: Craig Ringer
Дата:
Сообщение: Re: Need magic that checks if a collumn contains values that CAN NOT be converted to Latin1
Следующее
От: Piotr Kublicki
Дата:
Сообщение: Re: pg_hba.conf