From: =?iso-8859-1?q?Rodrigo_De_Le=F3n?= <rdeleonp@gmail.com>
Subject: Re: UNIQUEness and time interval
Date: Mon, 08 Oct 2007 14:46:40 -0000
Organization: http://groups.google.com
Lines: 42
Message-ID: <1191854800.465075.65670@y42g2000hsy.googlegroups.com>
References: <fe2cns$vi1$1@guepard.ecp.fr>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
In-Reply-To: <fe2cns$vi1$1@guepard.ecp.fr>
User-Agent: G2/1.0
Complaints-To: groups-abuse@google.com
Injection-Info: y42g2000hsy.googlegroups.com; posting-host=200.75.231.230;
	posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
To: pgsql-sql@postgresql.org

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