Обсуждение: Problem: concat an array of arrays

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

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

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

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

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
>
>