...
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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
