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