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

centrum[2] = centrum[2] / natoms;
centrum[3] = centrum[3] / natoms;
Return centrum;
End;
$$ Language plpgsql;
This function takes one argument of the composite data type mol . Notice
that the argument(s) may be named in the function definition. Here, using
(xmol mol) allows the variable xmol to be used as the name of the input
argument instead of $1 . The function returns an array of three floats com-
puted as the center (average coordinate) of the input mol. There are two main
sections to every plpgsql function. The Declare section contains variable
names, their type, and initial values. The Begin section contains the code
that performs the necessary operations. There should always be a Return
statement. Using the moltest table defined above, the SQL statement
Select center(amol) From moltest Where name='1-methylthymine';
returns {-1.35168,-0.163,-0.10205} .
There are many other features of plpgsql that make it useful. There are
variables that can hold entire rows of a table, loops that iterate over rows
returned from a select statement, methods of handling errors, and ways of
executing dynamically generated SQL strings. These are described in the
on-line documentation 1 and topics on PostgreSQL. 2
10.5.2 Plperl, Plpython, Pltcl
There are several other procedural languages supported by PostgreSQL.
Among these are perl, python, and tcl. None of these languages is inher-
ently better than the others. It may be better to write functions using these
languages for several reasons. One of the reasons for using a given lan-
guage is a familiarity with the language. This might occur when a research
group has been developing code in one particular language for years. A
more compelling reason to use one language arises when there already
exists a set of modules that compute the desired results. For example, there
is a set of perl modules called PerlMol for molecular chemistry. 3 Consider
how this can be used to write an extension functions for PostgreSQL.
Plperl is not actually different than perl. It simply defines a protocol
for passing and returning arguments to and from a plperl function. This
means that arguments passed to a plperl function use the PostgreSQL
string external representation. For text and numeric arguments, this pres-
ents no problem, since perl routinely uses string representations, even for
numerical data.
The following plperl function will compute a molecular formula from
an input SMILES.