Re: Ranges for well-ordered types

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Ian Caulfield <ian(dot)caulfield(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Ranges for well-ordered types
Date: 2006-06-10 17:34:31
Message-ID: EBFB010E-1FFA-42DF-BE1D-B9F11484EA62@seespotcode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Glaesemann 2006-06-10 17:39:30 Re: Ranges for well-ordered types
Previous Message Greg Stark 2006-06-10 17:30:14 Re: ADD/DROP INHERITS