Re: UNIQUEness and time interval - Mailing list pgsql-sql

From Rodrigo De León
Subject Re: UNIQUEness and time interval
Date
Msg-id 1191854800.465075.65670@y42g2000hsy.googlegroups.com
Whole thread Raw
List pgsql-sql
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



pgsql-sql by date:

Previous
From: "A. Kretschmer"
Date:
Subject: Re: Get different sums from the same table in one query
Next
From: Daniel Drotos
Date:
Subject: array handling on 8.0.3