-- -- time_interval__overlaps_p/3 -- create or replace function time_interval__overlaps_p( integer, timestamp with time zone, timestamp with time zone ) returns bool as $$ declare overlaps_p__interval_id alias for $1; overlaps_p__start_date alias for $2; -- default null, overlaps_p__end_date alias for $3; -- default null v_interval_start time_intervals.start_date%TYPE; v_interval_end time_intervals.end_date%TYPE; begin -- Pull out the start and end date and call the main overlaps_p. select start_date, end_date into v_interval_start, v_interval_end from time_intervals where interval_id = overlaps_p__interval_id; return time_interval__overlaps_p( v_interval_start, v_interval_end, overlaps_p__start_date, overlaps_p__end_date ); end;$$ language plpgsql; -- -- time_interval__overlaps_p/2 -- create or replace function time_interval__overlaps_p( integer, integer ) returns bool as $$ declare overlaps_p__interval_id_1 alias for $1; overlaps_p__interval_id_2 alias for $2; v_start_1 timestamptz; v_start_2 timestamptz; v_end_1 timestamptz; v_end_2 timestamptz; begin -- Pull out the start and end dates and call the main overlaps_p. select start_date, end_date into v_start_1, v_end_1 from time_intervals where interval_id = overlaps_p__interval_id_1; select start_date, end_date into v_start_2, v_end_2 from time_intervals where interval_id = overlaps_p__interval_id_2; return time_interval__overlaps_p( v_start_1, v_end_1, v_start_2, v_end_2 ); end;$$ language plpgsql; -- -- time_interval__overlaps_p/4 -- create or replace function time_interval__overlaps_p( timestamp with time zone, timestamp with time zone, timestamp with time zone, timestamp with time zone ) returns bool as $$ declare overlaps_p__start_1 alias for $1; overlaps_p__end_1 alias for $2; overlaps_p__start_2 alias for $3; overlaps_p__end_2 alias for $4; begin -- JS: Modified yet another deeply nested if-else-if -- JS: Note that null date is the representation for infinite -- (positive or negative) time. if overlaps_p__start_1 is null then -- No overlap if 2nd interval starts after 1st ends if overlaps_p__end_1 < overlaps_p__start_2 then return false; else return true; end if; end if; if overlaps_p__start_2 is null then -- No overlap if 2nd interval ends before 1st starts if overlaps_p__end_2 < overlaps_p__start_1 then return false; else return true; end if; end if; -- Okay, both start dates are not null if overlaps_p__start_1 <= overlaps_p__start_2 then -- 1st starts before 2nd if overlaps_p__end_1 < overlaps_p__start_2 then -- No overlap if 1st ends before 2nd starts return false; else -- No overlap or at least one null return true; end if; else -- 1st starts after 2nd if overlaps_p__end_2 < overlaps_p__start_1 then -- No overlap if 2nd ends before 1st starts return false; else -- No overlap or at least one null return true; end if; end if; end;$$ language plpgsql;