Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

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")