Problem: concat an array of arrays

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