By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE country(entity_id bigint, valid tsrange);
INSERT INTO country VALUES
(1, '[2020-10-10 10:10:10, 2020-10-10 11:11:11)')
,(1, '[2020-12-12 12:12:12, 2020-12-12 13:13:13)'); -- note the gap bvetween rows
2 rows affected
CREATE OR REPLACE AGGREGATE range_merge(anyrange) (
SFUNC = range_merge
, STYPE = anyrange
);
CREATE OR REPLACE FUNCTION aggregate_validity(entity_name regclass, entry bigint, OUT result tsrange)
LANGUAGE plpgsql AS
$func$
BEGIN
EXECUTE 'SELECT range_merge(valid) FROM ' || entity_name ||' WHERE entity_id = $1'
INTO result
USING entry;
END
$func$;
TABLE country;
| entity_id | valid |
|---|---|
| 1 | ["2020-10-10 10:10:10","2020-10-10 11:11:11") |
| 1 | ["2020-12-12 12:12:12","2020-12-12 13:13:13") |
SELECT aggregate_validity('country', 1);
| aggregate_validity |
|---|
| ["2020-10-10 10:10:10","2020-12-12 13:13:13") |