Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE country(entity_id BIGSERIAL PRIMARY KEY, valid tsrange, registration tsrange);
CREATE TABLE airport(entity_id BIGSERIAL PRIMARY KEY, valid tsrange, registration tsrange,
country_key bigint references country (entity_id));

INSERT INTO country 
VALUES(1,'[2016-06-22 19:10:25-07, 2019-01-01 00:00:00)', tsrange(now()::timestamp, '9999-01-01 00:00:00'));
INSERT INTO airport VALUES(1,'[2018-06-22 19:10:25-07, 2020-01-01 00:00:00)', tsrange(now()::timestamp, '9999-01-01 00:00:00'), 1);
1 rows affected

1 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) returns tsrange AS
$$
DECLARE
    result tsrange;
BEGIN
   EXECUTE format('select range_merge(valid) from %I where entity_id = %s', entity_name, entry) into result;
   return result;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION ingoing_outgoing_reference_integrity_breach(altered_table_name text, entity_ids bigint[])  
RETURNS TABLE(
	   source_entity text,
	   source_entry_id bigint,
	   source_entry_validity tsrange,
       is_included_in_timeline boolean,
       aggregated_foreing_entry_validity tsrange,
	   foreing_entry_id bigint,
	   foreing_entity text,
	   is_reference_ingoing boolean
      )
AS $$
DECLARE
    foreign_key  record;
	is_reference_ingoing boolean;
BEGIN

    FOR foreign_key  IN (
        --  Using the information schema from postgres, all constraints of type 
        --  FOREIGN_KEY, is extracted. For each of the foreing key constraint, the 
        --  source_table and foreing_table which is linked by this constraint is then extracted.  
        --  to ensure both ingoing references and outgoing references are extracted from this query, is
        --  condition provided as such that either the source_table = altered_table_name OR foreing_table_name = altered_table_name||'_registration'
        SELECT
            tc.table_name AS source_table, 
			kcu.column_name as column_attribute,
            ccu.table_name AS foreign_table
        FROM 
            information_schema.table_constraints AS tc 
            JOIN information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
            JOIN information_schema.constraint_column_usage AS ccu
            ON ccu.constraint_name = tc.constraint_name
        WHERE 
            constraint_type = 'FOREIGN KEY' and 
            (ccu.table_name = altered_table_name OR tc.table_name = altered_table_name) and
			ccu.column_name != 'row_id' and 
            kcu.column_name != 'entity_id') 
	LOOP
	---    Select an source entity_id, which have a lookup to a foreing table,
	---    to any provided entity_ids, and return a boolean whether the aggregated_validity 
	---    of the foreing table entry, contains te validity period of the source table. 
  		DROP TABLE IF EXISTS lookup_breach;
		EXECUTE format('CREATE TEMP TABLE lookup_breach ON COMMIT DROP AS
					   SELECT '||foreign_key.source_table||'.entity_id AS source_entry_id, -- The Id of the source entry
					'||foreign_key.source_table||'.'||foreign_key.column_attribute||' AS foreing_entry_id, -- The id of the foreing entry
           			'||foreign_key.source_table||'.valid AS source_entry_valid, -- the validity period of the source entry
					aggregate_validity('||foreign_key.foreign_table||', '||foreign_key.source_table||'.'||foreign_key.column_attribute||') AS foreing_entry_valid, -- the validity period of the foreing entry
					aggregate_validity('||foreign_key.foreign_table||', '||foreign_key.source_table||'.'||foreign_key.column_attribute||') <@ '||foreign_key.source_table||'.valid as in_lifespan
           			FROM '||foreign_key.foreign_table||', '||foreign_key.source_table||'
           			WHERE '||foreign_key.source_table||'.'||foreign_key.column_attribute||' = ANY($1)
                    AND '||foreign_key.source_table||'.'||foreign_key.column_attribute||' = '||foreign_key.foreign_table||'_registration.entity_id
           			AND '||foreign_key.source_table||'.registration @> now()::timestamp
           			GROUP BY '||foreign_key.source_table||'.'||foreign_key.column_attribute||', 
				   	'||foreign_key.source_table||'.entity_id,'||foreign_key.source_table||'.valid,
					'||foreign_key.foreign_table||'.valid' ) using entity_ids;

		RETURN QUERY SELECT foreign_key.source_table::text, lookup_breach.source_entry_id, lookup_breach.source_entry_valid, lookup_breach.in_lifespan , lookup_breach.foreing_entry_valid,
        lookup_breach.foreing_entry_id, foreign_key.foreign_table::text, foreign_key.foreign_table::text= altered_table_name AS is_reference_ingoing
		FROM lookup_breach;
	END LOOP;
END
$$ LANGUAGE plpgsql;
select * from ingoing_outgoing_reference_integrity_breach('country'::text, '{1}')
ERROR:  function aggregate_validity(country, bigint) does not exist
LINE 5:      aggregate_validity(country, airport.country_key) AS for...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  CREATE TEMP TABLE lookup_breach ON COMMIT DROP AS
					   SELECT airport.entity_id AS source_entry_id, -- The Id of the source entry
					airport.country_key AS foreing_entry_id, -- The id of the foreing entry
           			airport.valid AS source_entry_valid, -- the validity period of the source entry
					aggregate_validity(country, airport.country_key) AS foreing_entry_valid, -- the validity period of the foreing entry
					aggregate_validity(country, airport.country_key) <@ airport.valid as in_lifespan
           			FROM country, airport
           			WHERE airport.country_key = ANY($1)
                    AND airport.country_key = country_registration.entity_id
           			AND airport.registration @> now()::timestamp
           			GROUP BY airport.country_key, 
				   	airport.entity_id,airport.valid,
					country.valid
CONTEXT:  PL/pgSQL function ingoing_outgoing_reference_integrity_breach(text,bigint[]) line 33 at EXECUTE