Re: Problem: concat an array of arrays

On Thu, Apr 29, 2010 at 8:46 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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?

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