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_ratesWHERE 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