Consider the following code:
with a(x) as(
values
('{[10,20],[100,200]}'::int4multirange)
,('{[20,40],[15,55],[100,200]}'::int4multirange)
)
select range_agg(x)
from (
select unnest(x)x
from a
)t;
=> {[10,56),[100,201)}
Here, range_agg() won’t accept a multirange type input, but it outputs a multirange type.
The only way to aggregate the source multirange rows is to unnest them first.
However, range_intersect_agg() does not require the unnest step:
with a(x) as(
values
('{[10,20],[100,200]}'::int4multirange)
,('{[20,40],[15,55],[100,200]}'::int4multirange)
)
select range_intersect_agg(x)
from a;
=> {[15,21),[100,201)}
Ian Campbell