range_agg() missing support for multirange inputs

From: Ian Campbell <Ian(at)PrimeAbility(dot)co(dot)za>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: range_agg() missing support for multirange inputs
Date: 2021-11-03 17:39:52
Message-ID: JNZP275MB0737BDCCB3792619F1DF04128A8C9@JNZP275MB0737.ZAFP275.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Browse pgsql-bugs by date

  From Date Subject
Next Message Daniel Gustafsson 2021-11-03 21:03:33 Re: BUG #17262: "View manual" button on postgres.org/docs is overflowing horizontally on mobile view
Previous Message Tom Lane 2021-11-03 16:46:38 Re: INFORMATION_SCHEMA.routines column routine_definition does not show the source