[SQL] check for overlapping time intervals

2013-04-22 Thread Wolfgang Meiners 
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

Re: [SQL] check for overlapping time intervals

2013-04-22 Thread 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





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] check for overlapping time intervals

2013-04-22 Thread Wolfgang Meiners 
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





-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql