PostgreSQL Extensions - Design and Use of Relational Databases in Chemistry - page 113

Since the range _ float function only interprets exact(=) range values
as float , null is returned whenever a range value contains < or >. So any
function like sqrt , will also return a null value. Since range values can
now be implicitly interpreted as float , many useful functions, such as
sqrt , max , and avg and operators such as + , − and * become available
for range data. These return the expected value for exact ranges, but
null for < and > values.
The < and > operators of SQL can also be used with range data. However,
since the conversion from range to float returns null for range data
containing < and >, the following SQL only selects exact range values.
Select ic50::text, ic50::float, sqrt(ic50) from rangetest
Where ic50 > 10;
If the table contains range values, such as 20 or 30, these will be correctly
selected. However, if the table also contains range values such as >10, >20,
and >30, these will not be selected using the above SQL.
In order to properly compare range values it is necessary to define
functions that operate directly on the range data type, rather than indi-
rectly after the implicit conversion to float . The following functions
define how two range values should be compared for equality, less than,
greater than, etc.
Create Function range_cmp(range, range) Returns Integer As $$
Select Case
When ($1).q = ($2).q And ($1).v = ($2).v Then 0
When ($1).q = '=' And ($2).q = '=' And ($1).v < ($2).v Then -1
When ($1).q = '=' And ($2).q = '=' And ($1).v > ($2).v Then 1
When ($1).v = ($2).v And (
( ($1).q = '<' And ($2).q = '=' ) Or
( ($1).q = '<' And ($2).q = '>' ) Or
( ($1).q = '=' And ($2).q = '>' )) Then -1
When ($1).v = ($2).v And (
( ($1).q = '>' And ($2).q = '=' ) Or
( ($1).q = '>' And ($2).q = '<' ) Or
( ($1).q = '=' And ($2).q = '<' )) Then 1
When ($1).v < ($2).v And (
( ($1).q = '<' And ($2).q = '=' ) Or
( ($1).q = '<' And ($2).q = '>' ) Or
( ($1).q = '=' And ($2).q = '>' )) Then -1
When ($1).v > ($2).v And (
( ($1).q = '>' And ($2).q = '=' ) Or
( ($1).q = '=' And ($2).q = '<' ) Or
( ($1).q = '>' And ($2).q = '<' )) Then 1
Else Null
End;
$$ Language Sql;
Create Function range_eq(range, range) Returns Boolean As $$
Select ($1).q = ($2).q And ($1).v = ($2).v;
$$ Language SQL;
Create Function range_ne(range, range) Returns Boolean As $$
Select ($1).q != ($2).q Or ($1).v != ($2).v;