From: | Wolfgang Meiners <WolfgangMeiners01(at)web(dot)de> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | check for overlapping time intervals |
Date: | 2013-04-22 10:19:17 |
Message-ID: | kl32r0$850$1@ger.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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, --- start
e TIME NOT NULL, --- end
CHECK (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 $$
BEGIN
IF TG_OP = 'INSERT' THEN
IF EXISTS(
SELECT * FROM timetable
WHERE gid = NEW.gid AND day = NEW.day
AND s < NEW.e AND e > NEW.s)
THEN
RAISE EXCEPTION 'overlapping intervals';
END IF;
ELSIF TG_OP = 'UPDATE' THEN
IF EXISTS(
SELECT * FROM timetable
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2013-04-22 10:36:22 | Re: check for overlapping time intervals |
Previous Message | Pavel Stehule | 2013-04-20 16:59:05 | Re: [SQL] Table indexes in a SELECT with JOIN´s |