The basic capabilities of a RDBMS are accessible using the SQL language. These
capabilities include the basic data types, such and numeric, text, date, etc.
and basic functions and operators, such as length, sqrt, = and like. It is possible
to extend the capabilities of the database and of SQL by defining new data types
and new functions. These integrate neatly into the syntax of SQL and allow the
new data types and functions to be easily used in ways similar to the standard
SQL data types and functions. The PostgreSQL RDBMS allows the use of various
computer languages to create new functions, including a procedural language
plpgsql native to PostgreSQL. The plpgsql language is analogous, but substantially
different than the sqlplus language used in the Oracle RDBMS. Of course, it
is possible to simply use SQL to define new data types and functions as well.
In this chapter the focus is on PostgreSQL and the various languages available
to extend its functionality.
Chapter 3 showed how SQL could be used to write a function to convert pressure
data values expressed in atmospheres to kilopascals. Other functions were used
in check constraints on a column containing CAS numbers. This chapter will show
how new data types can be defined. This will require functions to define the
method for input parsing and the method to output data values. There will also
be functions to define operations on the new data types, enabling searches to
be integrated easily with standard SQL syntax. [view
wordle] [hide wordle]
| Page |
|
| 109 |
Create Type conc As (val float, unit text); |
| 110 |
Create Table assay1 (id integer, ki float, ki_unit text, ic50 float, ic50_unit text, ec50 float, ec50_unit text);
Create Table assay1 (id integer, ki conc, ic50 conc, ec50 conc);
Select ic50, ec50 From assay1 Where id = 47665;
Select (ic50).val as "ic50(nM)", (ec50).val||(ec50).unit as ec50
From assay1 where (ic50).val < 10 and (ic50).unit = 'nM';
|
| 111 |
Create Type range As (op Text, val Float);
Create Table rangetest (smiles text, ic50 range, name text);
Select * from rangetest;
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; |
| 112 |
Insert Into rangetest (ic50) Values ( range_parse('<27.5') );
Select range_text(ic50) from rangetest;
Create Cast (range as text) With Function range_text(range) As Implicit;
Select ic50::text from rangetest;
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;
Select ic50, ic50::text, ic50::float, sqrt(ic50) from rangetest; |
| 113 |
Select ic50::text, ic50::float, sqrt(ic50) from rangetest Where ic50 > 10;
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;
$$ 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;
|
| 114 |
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 = >);
Select ic50::text, ic50::float, sqrt(ic50) from rangetest
Where ic50 > range_parse(10); |
| 115 |
Create table ctest (smiles text, name text, coord float[][3]);
Insert Into coordtest (smiles,name,coord) Values
('CN1C=C(C)C(=O)NC1=O','1-methylthymine',
'{
{-0.5223,-1.2374,0.2579},
{-1.8677,-1.3917,0.1177},
{-2.7245,-0.3893,-0.2291},
{-2.2127,0.8622,-0.4679},
{-0.8652,1.0783,-0.3284},
{0.0270,0.0456,0.0406},
{-3.9604,-0.6494,-0.3168},
{0.1995,-2.2271,0.5751},
{1.4782,0.3157,0.1674},
{-3.0687,1.9631,-0.8370}
}');
Select smiles, coord[1:2] From coordtest Where name='1-methylthymine';
|
| 116 |
Insert Into coordtest (smiles,name,coord) Values
('CN1C=C(C)C(=O)NC1=O','1-methylthymine',
'{
{-0.0000,-0.8250},
{0.7145,-0.4125},
{0.7145,0.4125},
{0.0000,0.8250},
{-0.7145,0.4125},
{-0.7145,-0.4125},
{1.4289,0.8250},
{-0.0000,-1.6500},
{-1.4289,-0.8250},
{0.0000,1.6500}
}');
Select smiles, coord from coordtest where array_upper(coord,2) = 2;
Create Type mol (smiles Text, coord Float[][3]);
Create Table moltest (amol mol, name Text);
Insert Into moltest (amol,name) Values (
'CN1C=C(C)C(=O)NC1=O',
'{
{-0.5223,-1.2374,0.2579},
{-1.8677,-1.3917,0.1177},
{-2.7245,-0.3893,-0.2291},
{-2.2127,0.8622,-0.4679},
{-0.8652,1.0783,-0.3284},
{0.0270,0.0456,0.0406},
{-3.9604,-0.6494,-0.3168},
{0.1995,-2.2271,0.5751},
{1.4782,0.3157,0.1674},
{-3.0687,1.9631,-0.8370}
}'),
'1-methylthymine');
|
| 117 |
Create Function center(xmol mol) Returns float[3] As $$
Declare
centrum Float[3] := Array[0., 0., 0.];
natoms Integer := array_upper((xmol).coord, 1);
i Integer;
Begin
For i In 1 .. natoms Loop
centrum[1] = centrum[1] + (xmol).coord[i][1];
centrum[2] = centrum[2] + (xmol).coord[i][2];
centrum[3] = centrum[3] + (xmol).coord[i][3];
End Loop;
centrum[1] = centrum[1] / natoms;
centrum[2] = centrum[2] / natoms;
centrum[3] = centrum[3] / natoms;
Return centrum;
End;
$$ Language plpgsql; |
| 118 |
Select center(amol) From moltest Where name='1-methylthymine'; |
| 119 |
Create Or Replace Function MF(text) Returns Text As $EOPERL$
use Chemistry::File::SMILES;
use Chemistry::File::Formula;
my ($s) = @_;
my $mol = Chemistry::Mol->parse($s, format => 'smiles');
return $mol->print(format => formula);
$EOPERL$ Language plperlu; |
| 120 |
Create or Replace Function oe_smiles(Text, integer)
Returns Text As 'gnova', 'oe_anysmiles'
Language 'c' Immutable Strict;
Comment On Function oe_smiles(Text, integer) Is 'smiles to smiles of various types'; |
![[view wordle]](javascript:show_wordle('chapter10.jpg')){kind=link}