Problem: concat an array of arrays

From: Belka Lambda <lambda-belka(at)yandex(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem: concat an array of arrays
Date: 2010-04-29 00:48:28
Message-ID: 31681272502108@web73.yandex.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jaime Rodriguez 2010-04-29 01:08:48 Performance and Clustering
Previous Message Jaime Casanova 2010-04-28 22:19:48 Re: Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints