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;
';
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
