Re: aggregate arrays

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