On Oct 4, 4:45 am, Nicolas Boullis <nicolas.boul...@ecp.fr> wrote:
> I'd like to define a table with a "name", a "start_date" and a
> "stop_date" columns, with a constraint that ensures that 2 records with
> ovelapping dates don't share the same name. Is there a way to define
> such a constraint?
CREATE TABLE T( NAME TEXT ,START_DATE DATE ,STOP_DATE DATE
);
CREATE OR REPLACE FUNCTION F() RETURNS TRIGGER AS $$ BEGIN IF EXISTS ( SELECT 1 FROM T WHERE NAME =
NEW.NAME AND (START_DATE, STOP_DATE) OVERLAPS (NEW.START_DATE, NEW.STOP_DATE) OR STOP_DATE =
NEW.START_DATE ) THEN RAISE EXCEPTION 'WHATCHA DOIN'' FOO'; RETURN NULL; END IF; RETURN NEW; END;
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER T_T
BEFORE INSERT ON T
FOR EACH ROW
EXECUTE PROCEDURE F();
INSERT INTO T VALUES ('FOO','2007-1-1','2007-1-3'); -- OK
INSERT INTO T VALUES ('FOO','2007-1-4','2007-1-6'); -- OK
INSERT INTO T VALUES ('FOO','2007-1-6','2007-1-8'); -- WILL BARF
