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_foreign_entry_validity  tsrange
              , foreign_entry_id                   bigint
              , foreign_entity                     text
              , is_reference_ingoing               boolean)
  LANGUAGE plpgsql AS
$func$
DECLARE
   foreign_key record;
   is_reference_ingoing boolean;

BEGIN
   FOR foreign_key IN
      SELECT tc.table_name   AS source_table
           , kcu.column_name
           , ccu.table_name  AS foreign_table
      FROM   information_schema.table_constraints       tc 
      JOIN   information_schema.key_column_usage        kcu USING (constraint_name)
      JOIN   information_schema.constraint_column_usage ccu USING (constraint_name)
      WHERE  tc.constraint_type = 'FOREIGN KEY'
      AND    _altered_table_name IN (ccu.table_name, tc.table_name)
      AND    ccu.column_name <> 'row_id'
      AND    kcu.column_name <> 'entity_id'

   LOOP
      RETURN QUERY EXECUTE format(
         'SELECT %1$L                                              -- AS source_entity
               , %1$I.entity_id                                    -- AS source_entry_id     -- the ID of the source entry
               , %1$I.valid                                        -- AS source_entry_valid, -- the validity period of the source entry
               , aggregate_validity(%3$L::regclass, %1$I.%2$I) <@ %1$I.valid -- AS in_lifespan
               , aggregate_validity(%3$L::regclass, %1$I.%2$I)               -- AS foreign_entry_valid -- the validity period of the foreign entry
               , %1$I.%2$I                                         -- AS foreign_entry_id    -- the ID of the foreign entry
               , $1                                                -- AS foreign_entity
               , $2                                                -- AS is_reference_ingoing
          FROM   %3$I, %1$I
          WHERE  %1$I.%2$I = ANY($3)
          AND    %1$I.%2$I = %3$I.entity_id  -- %3$I_registration.entity_id ???
          AND    %1$I.registration @> now()::timestamp
          GROUP  BY %1$I.entity_id
                  , %1$I.valid
                  , %1$I.%2$I
                  , %3$I.valid'  -- WHY this one ???
      , foreign_key.source_table    -- %1$I and %1$L
      , foreign_key.column_name     -- %2$I
      , foreign_key.foreign_table   -- %3$I and %3$L
      )
      USING foreign_key.foreign_table::text                        -- $1
          , foreign_key.foreign_table::text = _altered_table_name  -- $2
          , _entity_ids;                                           -- $3;
   END LOOP;
END
$func$;
select * from ingoing_outgoing_reference_integrity_breach('country'::text, '{1}')


source_entity
source_entry_id
source_entry_validity
is_included_in_timeline
aggregated_foreign_entry_validity
foreign_entry_id
foreign_entity
is_reference_ingoing


airport
1
["2018-06-22 19:10:25","2020-01-01 00:00:00")
f
["2016-06-22 19:10:25","2019-01-01 00:00:00")
1
country
t