Обсуждение: check for overlapping time intervals
Hi, I am on postgresql 9.1 and use at table like CREATE TABLE timetable(tid INTEGER PRIMARY KEY,gid INTEGER REFERENCES groups(gid),day DATE,s TIME NOT NULL, ---starte TIME NOT NULL, --- endCHECK (e > s)); Now, i need a constraint to prevent overlapping timeintervals in this table. For this, i use a trigger: CREATE OR REPLACE FUNCTION validate_timetable() RETURNS trigger AS $$ BEGINIF TG_OP = 'INSERT' THEN IF EXISTS( SELECT * FROM timetable WHERE gid = NEW.gid AND day = NEW.day AND s < NEW.e ANDe > NEW.s) THEN RAISE EXCEPTION 'overlapping intervals'; END IF;ELSIF TG_OP = 'UPDATE' THEN IF EXISTS( SELECT * FROMtimetable WHERE gid = NEW.gid AND day = NEW.day AND tid <> OLD. tid AND s < NEW.e AND e > NEW.s) THEN RAISE EXCEPTION'overlapping intervals'; END IF;END IF;RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER validate_timetable BEFORE INSERT OR UPDATE ON timetable FOR EACH ROW EXECUTE PROCEDURE validate_timetable(); Is there a simpler way to check for overlapping timeintervals? I ask this question, because i have more similar tables with similar layout and would have to write similar functions again and again. Thank you for any hints Wolfgang
Wolfgang Meiners, 22.04.2013 12:19: > Is there a simpler way to check for overlapping timeintervals? I ask > this question, because i have more similar tables with similar layout > and would have to write similar functions again and again. Do you have the possibility to upgrade to 9.2? The range types introduced with 9.2 seem to be *exactly* what you need. Regards Thomas
Am 22.04.13 12:36, schrieb Thomas Kellerer: > Wolfgang Meiners, 22.04.2013 12:19: >> Is there a simpler way to check for overlapping timeintervals? I ask >> this question, because i have more similar tables with similar layout >> and would have to write similar functions again and again. > > Do you have the possibility to upgrade to 9.2? > > The range types introduced with 9.2 seem to be *exactly* what you need. > > Regards > Thomas > Hi Thomas, after looking a bit i found you are right. There may be some hacks to do it without range types, but with range types it is straigt forward to implement. I am not quite happy to upgrade to 9.2 because i just got everything running fine on 9.1. But i think, i will do it this way. Thank you for your help. Wolfgang