On Oct 4, 4:45 am, Nicolas Boullis <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
