Обсуждение: aggregate arrays

example:

a query returns a column that contains arrays:

select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t;
   column1   | column2
-------------+---------
 {1,2,3,3,4} |       1
 {1,2,2,3,4} |       2
(2 rows)

and then we want aggregate that result.

example by column2:

WITH "test" AS (
    select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t
)

SELECT array_agg(column2) column2 FROM "test";
 column2
---------
 {1,2}
(1 row)


and I want aggregate column1 arrays into one array. I want receive the
result:

       column1        |  column2
----------------------+-------------
{1,2,3,3,4,1,2,2,3,4} | {1,2}


I've tried the statement:

WITH "test" AS (
    select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t
)

SELECT
    array_agg(unnest(column1)) AS column1,
    array_agg(column2) column2
FROM
    "test";

But I receive the error:

ERROR:  set-valued function called in context that cannot accept a set

How can I aggregate arrays into one array?
--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
  `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

Dmitry E. Oboukhov wrote:
> example:
> 
> a query returns a column that contains arrays:
> 
> select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t;
>    column1   | column2
> -------------+---------
>  {1,2,3,3,4} |       1
>  {1,2,2,3,4} |       2
> (2 rows)
> 
> and then we want aggregate that result.
> 
> example by column2:
> 
> WITH "test" AS (
>     select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t
> )
> 
> SELECT array_agg(column2) column2 FROM "test";
>  column2
> ---------
>  {1,2}
> (1 row)
> 
> 
> and I want aggregate column1 arrays into one array. I want receive the
> result:
> 
>        column1        |  column2
> ----------------------+-------------
> {1,2,3,3,4,1,2,2,3,4} | {1,2}
> 
> 
> I've tried the statement:
> 
> WITH "test" AS (
>     select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t
> )
> 
> SELECT
>     array_agg(unnest(column1)) AS column1,
>     array_agg(column2) column2
> FROM
>     "test";
> 
> But I receive the error:
> 
> ERROR:  set-valued function called in context that cannot accept a set
> 
> How can I aggregate arrays into one array?

Create your own aggregate.

CREATE AGGREGATE array_union (anyarray) (SFUNC = array_cat, STYPE = anyarray);

WITH "test" AS (
    select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t
)
SELECT
    array_union(column1) AS column1,
    array_agg(column2) column2
FROM
    "test";

        column1        | column2
-----------------------+---------
 {1,2,3,3,4,1,2,2,3,4} | {1,2}
(1 row)

Yours,
Laurenz Albe