From: Wolfgang Meiners <WolfgangMeiners01@web.de>
To: pgsql-sql@postgresql.org
Subject: check for overlapping time intervals
Date: Mon, 22 Apr 2013 12:19:17 +0200
Message-ID: <kl32r0$850$1@ger.gmane.org> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: WolfgangMeiners01@web.de
Subject: Re: check for overlapping time intervals
In-Reply-To: <kl32r0$850$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox