range_agg() missing support for multirange inputs

Started by Ian Campbellover 4 years ago1 messagesbugs
Jump to latest
#1Ian Campbell
Ian@PrimeAbility.co.za

Hello,

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)}

Best,
Ian Campbell