Message-ID: <3F6CDAE6.5010407@houle.org>
Date: Sat, 20 Sep 2003 18:55:34 -0400
From: Kevin Houle <kevin@houle.org>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.4) Gecko/20030624
MIME-Version: 1.0
To: Andrew Milne <amilne@solutioninc.com>
Subject: Re: Unique Constraint Based on Date Range
References: <3F69FC7F.1040103@solutioninc.com>
In-Reply-To: <3F69FC7F.1040103@solutioninc.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 50
To: pgsql-sql@postgresql.org

Andrew Milne wrote:

...
> create table rates (
>    effective_date AS timestamp,
>    expiry_date AS timestamp,
>    cost AS numeric (12,2),
>     access_time AS integer  (in minutes)
> );
> 
> So for a given cost, there may not be a record where the effective date 
> of one record overlaps the expiry date of another (and vice versa).
> 
> Example record set (effective date, expiry date, cost, access_time):
> 
> 2003-01-01 | 2003-01-15 | 5.00 | 60
> 2003-01-15 |  infinity | 5.00 | 120
> 2003-01-01 | infinity | 1.00 | 10
> 
> An attempt to insert another 5.00 rate effective now would fail, because 
> a 5.00 rate exists that doesn't expire (i.e. the expiry date would have 
> to be updated to the effective date of the new record minus 1 second).
> 
> I can enforce this from the front end, but a db constraint would be great.

I don't know that a CHECK constraint would allow you to do this.
But, you could create a function to perform the check, and fire
a trigger on INSERT or UPDATE to execute the function. For example,
something like this might do the trick.

CREATE FUNCTION "check_record" () RETURNS TRIGGER AS '
   DECLARE
     result RECORD;
   BEGIN
     SELECT INTO result * FROM table_rates WHERE
       effective_date >= NEW.effective_date AND
       expiry_date <= NEW.expiry_date AND
       cost = NEW.cost;
     IF FOUND THEN
        RAISE EXCEPTION ''record overlaps with existing record'';
     END IF;
     RETURN NEW;
   END; ' LANGUAGE 'plpgsql';

CREATE TRIGGER "tg_check_record"
   BEFORE INSERT OR UPDATE ON table_rates
   FOR EACH ROW EXECUTE PROCEDURE "check_record" ();

Kevin