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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
