Appendix - Design and Use of Relational Databases in Chemistry - page 191

my @match_list = ();
my @matches = ();
my $nmatch = 0;
while ( $patt->match($mol) ) {
$nmatches = @matches = $patt->atom_map;
for (my $i=0; $i<$nmatches; ++$i) {
$matches[$i] = $atom_number{$matches[$i]};
}
#-- braces make this a postgresql array
$match_list[$nmatch] .= "{" . (join ",", @matches) . "}";
++$nmatch;
}
return undef unless ($nmatch > 0);
if ($nshow > 0 && $nshow <= $nmatch) {
return "$match_list[$nshow-1]";
} else {
#-- braces make this a postgresql array (of arrays)
return "{" . (join ",", @match_list) . "}";
}
$EOPERL$ Language plperlu;
Create Or Replace Function list_matches(text, text) Returns Text[]
As $EOSQL$
-- Convenience function to return first match
Select list_matches($1, $2, 1);
$EOSQL$ Language SQL;
In order to install these functions into a database named mydb , the follow-
ing command would be used. Assume that the code above is stored in a
file named perlmol-core.sql.
sudo -u postgres psql mydb <perlmol-core.sql
It is necessary to install these as the PostgreSQL superuser, here postgres,
or as any another PostgreSQL superuser. This is because the “untrusted”
language plperlu is used. The language plperlu must be used because of
the perl use statements in these functions. It might be more accurate to
say “unrestricted” language, since plperlu can use all the functionality of
perl. More information about the differences between plperl and plperlu
is available. 4
A.6.2 FROWNS/plpythonu
FROWNS 5 is an open source python module loosely based on Andrew
Dalke's PyDaylight. 6 It includes methods that operate on SMILES,
SMARTS, and molfiles as well as other functionality, including finger-
prints. Once FROWNS is installed, it can be used within any python pro-
gram. Using the plpythonu procedural language available in PostgreSQL,