On Jun 11, 2006, at 0:54 , Ian Caulfield wrote:
> I've done similar date range things by creating a composite type
> consisting of the lower and upper bounds, and then implementing a
> btree opclass where the comparator returns 0 if two ranges overlap
> - this allows a current btree index to enforce non-overlapping
> ranges, and allows indexed lookup of which range contains a
> particular value.
As Tom already pointed out, this method leads to problems with btree
indexes. I haven't heavily tested my own implementation (below), but
it only returns 0 for equality, which is what btree expects. All
other possible relationships between two ranges have a well-defined
result of -1 or 1. I believe this should be enough to prevent any
transitivity issues with btree.
Michael Glaesemann
grzm seespotcode net
create type interval_date as
( _1 point_date , _2 point_date
);
comment on type interval_date is
'The internal representation of date intervals, representing the
closed-closed '
'interval [_1,_2]';
create function interval_cmp( interval_date -- $1 i1 , interval_date -- $2 i2 ) returns integer
strict
immutable
security definer
language plpgsql as '
declare i1 alias for $1; i2 alias for $2; cmp integer;
begin perform check_intervals(i1,i2);
cmp := 1;
if i1._1 = i2._1 and i1._2 = i2._2 then cmp := 0; else if (i1._2 < i2._2) or (i1._2
=i2._2 and i1._1 > i2._1) then cmp = -1; end if; end if;
return cmp;
end;
';
