Re: Problem: concat an array of arrays

Thanks, Merlin! The "restack" function solves the problem! :)

> what are you trying to do w/unfold function exactly?
The recursive query I mentioned was to produce from the argument
array[array[1,2,3],array[4,5,6],array[7,8,9],array[10,11]]the result array[1,2,3,4,5,6,7,8,9,10,11]. 

The behaviour of the unnest function confused me, I didn't expect anything like that
----------------------------------------------------------------------
postgres=# select array(select unnest(array[array[1,2,3],array[4,5,6]]));
   ?column?
---------------
 {1,2,3,4,5,6}
(1 row)

postgres=# select array(select unnest(array[array[1,2,3],array[4,5]]));
ERROR:  multidimensional arrays must have array expressions with matching dimensions
---------------------------------------------------------------------
But, oh well, at least I can make a {...} from {{...}} in a functional way:)

Regards, Belka

29.04.10, 08:53, "Merlin Moncure" <mmoncure@gmail.com>:

> On Thu, Apr 29, 2010 at 8:46 AM, Merlin Moncure  wrote:
>  > On Wed, Apr 28, 2010 at 8:48 PM, Belka Lambda  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?
>
>  hm. the basic problem is that it's difficult to slide arrays up/down
>  dimensions.  you can move from scalars to arrays and arrays to
>  scalars, but not from dimension N to N-1 etc. you can however move
>  from dimension 'N' to 1:
>
>  create or replace function restack(_array anyarray) returns anyarray as
>  $$
>    select array(select unnest($1));
>  $$ language sql immutable;
>
>  select restack(array[1,2,3]);
>   restack
>  ---------
>   {1,2,3}
>
>  select restack(array[array[1,2,3]]);
>   restack
>  ---------
>   {1,2,3}
>
>
>  merlin
>
>