On Wed, Apr 28, 2010 at 8:48 PM, Belka Lambda <lambda-belka@yandex.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