If I have the following table:
create table my_table(
start TIME,
end TIME
);
and then I wanted a function as a trigger that would compare any INSERTS
or UPDATES to see if there was any overlap in the interval between start
and end of the new rows and existing rows, is there a single statement
that I can execute in the trigger function, or do I have to do my own
iteration of some kind.
starting with an empty table.....
INSERT INTO my_table( start, end ) VALUES( '2:00 PM'::time, '3:00
PM'::time ); // Should pass
INSERT INTO my_table( start, end ) VALUES( '4:00 PM'::time, '5:00
PM'::time ); // Should pass
INSERT INTO my_table( start, end ) VALUES( '5:00 PM'::time, '5:59:59
PM'::time ); // Should pass
INSERT INTO my_table( start, end ) VALUES( '5:59:59 PM'::time, '6:00
PM'::time ); // Should pass
INSERT INTO my_table( start, end ) VALUES( '6:00 PM'::time, '7:00
PM'::time ); // Should pass
INSERT INTO my_table( start, end ) VALUES( '7:45 PM'::time, '8:00
PM'::time ); // Should pass
INSERT INTO my_table( start, end ) VALUES( '7:30 PM'::time, '7:45:01
PM'::time ); // Should ***FAIL***
INSERT INTO my_table( start, end ) VALUES( '8:00 PM'::time, '9:00
PM'::time ); // Should pass