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}
It depends on what you work with. Seems like your base table holds text arrays text[].
In addition to these functions, any built-in or user-defined aggregate function can be used as a window function
array_agg() but it operates on scalar types, not on array types.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 tblSince 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.
You can use recursive CTE.
Data:
-- drop table if exists sample_data;
create table sample_data (id int, arr varchar[]);
insert into sample_data
values
(1,ARRAY['One','Two','Three']),
(2,ARRAY['Four','Six']),
(3,ARRAY['Seven']);
Query:
with recursive cte as (
select
id, arr, arr as merged_arr
from
sample_data
where
id = 1
union all
select
sd.id, sd.arr, cte.merged_arr || sd.arr
from
cte
join sample_data sd on (cte.id + 1 = sd.id)
)
select * from cte
Result:
1;{One,Two,Three};{One,Two,Three}
2;{Four,Six}; {One,Two,Three,Four,Six}
3;{Seven}; {One,Two,Three,Four,Six,Seven}
You need an UPDATE with a CTE that refers back to the previous id:
WITH prev AS (SELECT id, c_array AS prev_array FROM your_table)
UPDATE your_table
SET c_array = prev_array || array_field
FROM prev
WHERE id = prev.id + 1;
If you want to do this automatically, then you need a BEFORE INSERT trigger and in the trigger function you simply execute SELECT c_array || NEW.array_field INTO NEW.c_array FROM your_table WHERE id = NEW.id - 1 if - and only if - you set the id yourself. If the id comes from a sequence then you need an AFTER INSERT trigger with an update as described above.
