From: Paul Jungwirth <pj@illuminatedcomputing.com>
Subject: range_agg
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Message-ID: <16d71dc8-34cf-5ebd-1ce5-ccd93c0a14f9@illuminatedcomputing.com>
Date: Fri, 3 May 2019 15:56:41 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.6.1
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hello,

I wrote an extension to add a range_agg function with similar behavior 
to existing *_agg functions, and I'm wondering if folks would like to 
have it in core? Here is the repo: https://github.com/pjungwir/range_agg

I'm also working on a patch for temporal foreign keys, and having 
range_agg would make the FK check easier and faster, which is why I'd 
like to get it added. But also it just seems useful, like array_agg, 
json_agg, etc.

One question is how to aggregate ranges that would leave gaps and/or 
overlaps. So in my extension there is a one-param version that forbids 
gaps & overlaps, but I let you permit them by passing extra parameters, 
so the signature is:

     range_agg(r anyrange, permit_gaps boolean, permit_overlaps boolean)

Perhaps another useful choice would be to return NULL if a gap/overlap 
is found, so that each param would have three choices instead of just 
two: accept the inputs, raise an error, return a NULL.

What do people think? I plan to work on a patch regardless, so that I 
can use it for temporal FKs, but I'd appreciate some feedback on the 
"user interface".

Thanks,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com