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

Create or Replace Function ctable(text) Returns Setof Record As
$EOSQL$
-- Called with $1 as SQL selecting integer[] as bonds
-- Example caller:
-- select * from
-- ctable('select smiles_to_bonds(cansmiles) as bonds
-- from vla4.structure where name=''BMCL-1051-38''')
-- as (atom1 integer, atom2 integer, bond_order integer);
Declare
bonds Record;
b Record;
i Integer;
Begin
For b In Execute $1 Loop
For i in 1 .. array_upper(b.bonds,1) Loop
Select b.bonds[i][1], b.bonds[i][2], b.bonds[i][3] Into bonds;
Return Next bonds;
End Loop;
End Loop;
End;
$EOSQL$ Language plpgsql;
Create or Replace Function symbol_coords(text) Returns Setof Record
As $EOSQL$
-- Called with $1 as SQL selecting text[] as symbols, numeric[] as
coords
-- Example caller:
-- select * from
-- symbol_coords('select smiles_to_symbols(cansmiles) as symbols,
-- coords from vla4.structure where name=''BMCL-1051-38''')
-- as (symbol text, x numeric, y numeric, z numeric);
Declare
sym_coord Record;
sc Record;
i Integer;
Begin
For sc In Execute $1 Loop
For i in 1 .. array_upper(sc.symbols,1) Loop
Select sc.symbols[i], sc.coords[i][1], sc.coords[i][2],
sc.coords[i][3]
Into sym_coord;
Return Next sym_coord;
End Loop;
End Loop;
End;
$EOSQL$ Language plpgsql;
These functions are called with a string argument. This argument is an
SQL statement that is expected to provide the required information. For
ctable, this is an array of bonds. For symbol _ coords , these are an array
of symbols and an array of coordinates for each atom.