Chemistry Reference
In-Depth Information
These help in using the range data type, for example:
Insert Into rangetest (ic50) Values ( range_parse('<27.5') );
Select range_text(ic50) from rangetest;
ic50 |
-------+
10 |
<10 |
>10 |
<27.5 |
It is always possible to use the ordinary external representation of the
range data type using parentheses, but using
range _ format
and
range _ text
conforms to more common representations of data like
these. It is possible to automate the range to text conversion even more,
using the create cast SQL command, as follows.
Create Cast (range as text) With Function range_text(range)
As Implicit;
When this is done, the
range _ text
function is implicitly called whenever
necessary. So, the following SQL would produce the same table as shown
above.
Select ic50::text from rangetest;
Since the
range
data type is not a standard SQL data type, the standard
SQL operators cannot be used with this data type. However, new opera-
tors can be defined using SQL functions. Since the
range
data type is so
similar to the
float
data type, implicit conversion to
float
is appro-
priate. This automatically allows many of the standard SQL operators to
work with exact(=)
range
values.
Create Function range_float(range) Returns Float As $$
Select Case When ($1).q = '=' Then ($1).v End;
$$ Language SQL;
Create Cast (range as float) With Function range_float(range)
As Implicit;
As with the
range _ text
conversion, the
range _ float
conversion
allows
range
values to be converted to
float
, whenever possible. This
makes the following SQL work without explicit definition of the
sqrt
function for
range
data types.
Select ic50, ic50::text, ic50::float, sqrt(ic50) from rangetest;
ic50 | ic50 | ic50 | sqrt
---------+----------+----------+------------------
(=,10) | 10 | 10 | 3.16227766016838
(<,10) | <10
(>,10) | >10
(>,27.5) | <27.5
Search WWH ::
Custom Search