Cumulative array from previous rows array?

Question

Is there a query that would merge previous arrays into a cumulative array, that would result in:

id array_field   c_array
---------------------------------------------
1  {one,two}     {one,two}
2  {three}       {one,two,three}
3  {four,five}   {one,two,three,four,five}
4  {six}         {one,two,three,four,five,six}

Answer 1

It depends on what you work with. Seems like your base table holds text arrays text[].

• You can use any aggregate function as window function. Per documentation:

In addition to these functions, any built-in or user-defined aggregate function can be used as a window function

• There is array_agg() but it operates on scalar types, not on array types.
• But you can create your own aggregate function easily.

To aggregate array types, create this aggregate function:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

Details in this related answer:
Selecting data into a Postgres array

Now, the job is strikingly simple:

SELECT array_agg_mult(array_field) OVER (ORDER BY id) AS result_array
FROM   tbl

Since the aggregate is defined for polymorphic types, this works for any array type, not just text[].

SQL Fiddle including alternative solution for text representation in a list.