Aggregate ranges by merging

Question

There aren't any Aggregate functions for Range types in PostgreSQL.

How to aggregate ranges using a merge operation?

This solution works but does not look optimal:

CREATE TEMPORARY TABLE test_ranges (r int4range);
INSERT INTO test_ranges VALUES ('[4, 8)'), ('[12, 45)'), (NULL);

SELECT int4range(min(lower(r)), max(upper(r))) FROM test_ranges;
=> '[4,45)'

Answer 1

Create an aggregate using the range_merge(anyrange, anyrange) function.

CREATE AGGREGATE range_merge(anyrange)
(
  sfunc = range_merge,
  stype = anyrange
);

SELECT range_merge(r) FROM test_ranges;
=> '[4,45)'