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

10.3 Composite Data Type

for Experimental Values

In most databases, a single value is used to represent an experimental
measure. In many cases however, that value is meant to represent an upper
limit or lower limit. For example, when measuring an IC50, the assay is
sometimes limited in sensitivity and results are reported, for example as
<0.15. If only the value 0.15 is stored, this would be indistinguishable from
results where the value was measured to be equal to 0.15, or >0.15. This
situation is typically handled by creating another column containing a
symbol, < or > or null. When this is done, it complicates the search of
these values because two columns must be considered and there is no
built-in SQL function to perform the search required. This situation can
be handled neatly by introducing a new SQL data type that incorporates
a data value and a flag to denote < or > values.
The following SQL defines the type range , creates a sample table,
and shows a selection of data from the table. Populating the table with
data is not shown here.

Create Type range As (op Text, val Float);
Create Table rangetest (smiles text, ic50 range, name text);
Select * from rangetest;
smiles | ic50 | name
----------------+-------------+--------------------------
BrC(Br)C(Br)Br | (=,10) | 1,1,2,2-tetrabromoethane
OCCSCCCCCCCC | (<,10) | 2-(octylthio)-ethanol
NCCCCCCCC | (>,10) | n-octylamine

The = is used to denote exact values. As with the conc values described
above, keeping the value and the operator together in the same composite
data type is preferred over keeping them in separate columns, especially
when multiple value-operator pairs exist in the same table.
The external representation of this data type uses parentheses. This can
be awkward, so the following input and output functions are defined.

Create Function range_parse(text) Returns range As $$
Select Case
When substring($1, 1, 1) = '<' Then ('<',substring($1, 2))::range
When substring($1, 1, 1) = '>' Then ('>',substring($1, 2))::range
When substring($1, 1, 1) = '=' Then ('=',substring($1, 2))::range
Else ('=',substring($1, 1))::range
End;
$$ Language SQL;

Create Function range_text(range) Returns Text As $$
Select Case
When ($1).q = '=' Then (($1).v)::Text
Else (($1).q||($1).v)::Text
End;
$$ Language SQL;