Re: Ranges for well-ordered types

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;
';