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

{-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');
There are many ways in which coordinates might be stored and used in a
chemical relational database. These are considered more fully in Chapter 11.
10.5 Functions in Other Languages
The functions in this topic have so far used only SQL. Most of these have
used standard SQL although some have made use of PostgreSQL exten-
sions to SQL. PostgreSQL also allows functions to be written in other
languages. The plpgsql procedural language extends standard SQL with
common programming language constructs such as variables, if-then-
else constructs, for and while loops, and exception handling. The plperl
and plpython procedural languages are also available, but offer no spe-
cial advantages, except of course to those programmers proficient in those
particular languages. Finally, functions can be written in the C language.
Since PostgreSQL itself is written in C, these functions use the very same
data structures and functions as the RDBMS itself. Therefore, functions
written in C offer the greatest advantage for speed and efficiency.
10.5.1 Plpgsql
Creating a function in plpgsql is done in a way similar to the previous exam-
ples using the SQL language. The following function creation shows some
of the useful features of plpgsql and differences from the SQL language.
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;