Re: Problem: concat an array of arrays

On Wed, Apr 28, 2010 at 8:48 PM, Belka Lambda <lambda-belka(at)yandex(dot)ru> wrote:
> 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

array_cat requires too array arguments. you could rewrite your expression to
array_cat(array[u.rest[1], u.accum)
(i think, not quite sure what you are trying to do).

you can append scalars to arrays with the || operator:
select array[1,2,3] || 4;
?column?
-----------
{1,2,3,4}

you can kinda sorta slice an array using the slice method:
select (array[array[1,2,3], array[2,4,6]])[1:1];
array
-----------
{{1,2,3}}

what are you trying to do w/unfold function exactly?

merlin