From: Rodrigo De León <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: UNIQUEness and time interval
Date: Mon, 08 Oct 2007 14:46:40 -0000
Message-ID: <1191854800.465075.65670@y42g2000hsy.googlegroups.com> (raw)
In-Reply-To: <fe2cns$vi1$1@guepard.ecp.fr>
References: <fe2cns$vi1$1@guepard.ecp.fr>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rdeleonp@gmail.com
Subject: Re: UNIQUEness and time interval
In-Reply-To: <1191854800.465075.65670@y42g2000hsy.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox