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