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") |
-- nested call
SELECT id, aggregate_validity('country', id) AS tsr
FROM (
VALUES
(1), (2)
) t(id);
| id | tsr |
|---|---|
| 1 | ["2020-10-10 10:10:10","2020-12-12 13:13:13") |
| 2 | null |
-- equivalent without function: correlated subquery
SELECT id, (SELECT range_merge(valid) FROM country WHERE entity_id = id) AS tsr
FROM (
VALUES
(1), (2)
) t(id);
| id | tsr |
|---|---|
| 1 | ["2020-10-10 10:10:10","2020-12-12 13:13:13") |
| 2 | null |
-- equivalent without function: LATERAL subquery
SELECT t.id, r.tsr
FROM (
VALUES
(1), (2)
) t(id)
LEFT JOIN LATERAL (
SELECT range_merge(valid)
FROM country
WHERE entity_id = t.id
) AS r(tsr) ON true;
| id | tsr |
|---|---|
| 1 | ["2020-10-10 10:10:10","2020-12-12 13:13:13") |
| 2 | null |