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

$$ Language SQL;
Create Function range_lt(range, range) Returns Boolean As $$
Select range_cmp($1, $2) = -1
$$ Language SQL;
Create Function range_le(range, range) Returns Boolean As $$
Select range_cmp($1, $2) != 1
$$ Language SQL;
Create Function range_gt(range, range) Returns Boolean As $$
Select range_cmp($1, $2) = 1
$$ Language SQL;
Create Function range_ge(range, range) Returns Boolean As $$
Select range_cmp($1, $2) != -1
$$ Language SQL;
These functions could be used in SQL, but it is even more convenient to
define SQL operators that use these functions.
Create Operator = (Leftarg = range, Rightarg = range,
Procedure = range_eq, Commutator = =, Negator = !=);
Create Operator != (Leftarg = range, Rightarg = range,
Procedure = range_ne, Commutator = !=, Negator = =);
Create Operator < (Leftarg = range, Rightarg = range,
Procedure = range_lt, Commutator = >, Negator = >=);
Create Operator > (Leftarg = range, Rightarg = range,
Procedure = range_gt, Commutator = <, Negator = <=);
Create Operator >= (Leftarg = range, Rightarg = range,
Procedure = range_ge, Commutator = <=, Negator = <);
Create Operator <= (Leftarg = range, Rightarg = range,
Procedure = range_le, Commutator = >=, Negator = >);
Then, the following SQL selects the correct data from the table.
Select ic50::text, ic50::float, sqrt(ic50) from rangetest
Where ic50 > range_parse(10);
ic50 | ic50 | sqrt
---------+-----------+-----------------
>10 | |
20 | 20 | 4.47213595499958
>20 | |
30 | 30 | 5.47722557505166
>30 | |
99.3 | 99.3 | 9.96493853468249
Notice the use of range _ parse(10) . This is necessary to force the com-
parison to be done using two range values. If the clause Where ic50 > 10
were used, the SQL parser might choose to convert the range value ic50
to float , rather than convert the constant 10 to range .