The definitions of these functions are located in the gnova schema. There are corresponding tables in gnova as used in the examples below. For a general discussion of fragment based functions and how you might implement new ones, see below.
Function  SQL example 

amw  Select amw('c1ccccc1COCN'); Update nci.structure set molwt = amw(smiles);  takes about 5 minutes for 250,000 structures 
tpsa  Select tpsa('c1ccccc1COCN'); Update nci.structure set tpsa = tpsa(smiles);  takes about 5 minutes for 250,000 structures 
public166keys  Select public166keys('c1ccccc1COCN'); Update nci.structure set fkey = public166keys(smiles);  takes about 20 minutes for 250,000 structures 
amw(text Smiles) returns numeric
This function returns a value for the molecular weight of the structure given by the input Smiles. It uses a table of Smarts for each of the first 103 atoms in the periodic table and their corresponding average atomic weight. This table is gnova.amw. This table also contains entries for atoms with 1 to 6 implicit H atoms. Note: this will return an incorrect value if any of your Smiles contains atoms having more that 6 implicit H atoms. Note: this function should not be used extensively, since it is in our opinion the world's most inefficient computer of molecular weight. However, it is an excellent introductory example of how fragment Smarts can be used to compute molecular properties.
tpsa(text Smiles) returns numeric
This function returns a value for the polar surface area of the structure given by the input Smiles. It uses a table of Smarts for the fragments defined by Ertl, et. al. and their corresponding partial surface areas.^{1} This table is gnova.tpsa.
public166keys(text Smiles) returns bit
This function returns a bitstring fingerprint for the structure given by the input Smiles. It uses a table of Smarts for the 166 fragment keys published by MDL^{2} and a corresponding bit number to set. This table is gnova.public166keys.
Many molecular properties can be computed by considering the fragments making up the molecule and combining the fragment values to estimate the molecular value, for example: molecular weight, polar surface area and fragment key fingerprints.
Molecular weight is computed as a sum of atomic weights. The CHORD SQL function count_matches(Smiles,'[#6]') will return the number of carbon atoms in the input smiles. When this is multiplied by 12.01, the average atomic weight of carbon, and the process is repeated for each element in Smiles, the sum of these computations will be the average molecular weight.
First nine rows of table gnova.amw.

The atomic_smarts and atomic_weight are conveniently stored
in a table such as the one to the left. Using the builtin aggregate
function sum, we can compute the average molecular
weight of benzoic acid as:
The gnova.amw function is defined as the above SQL statement, but with $1 substituted for the benzoic acid smiles so that any smiles string can be used. Note: the same name gnova.amw is used for both the table and the function. This is not necessary; some view this as confusing, while some think it is elegant. Note: this function is presented as an example. While it is entirely correct and useable, we claim that it is the world's least efficient computer of molecular weight. 
Last six rows of table gnova.amw.

Since Smiles typically contain implicit H atoms which are not matched by '[#1]', there are six additional rows in the gnova.amw table to account for this. These Smarts will match atoms having one to six implicit H atoms. The corresponding extra H atom weights will be properly added to the sum in the gnova.amw function. If your database tables contain structures with atoms having more than six implicit H atoms, it should now be crystal clear how to add to the gnova.amw table to account for this. 
You could consider creating an analogous exact molecular weight table/function in which the atomic weights are replaced by the weight of the exact isotope instead of the average weight of atomic isotopes. This function would be useful in the analysis of mass spectra.
Polar surface area can be computed as a sum of parameterized partial surface areas based on atom types defined by Ertl, et. al.^{1} The table gnova.tpsa contains smarts(column smarts) and partial surface areas(column atom_psa) taken from that work. The function gnova.tpsa uses SQL and the data in that table to sum the partial surface areas for atoms which match each smarts. The tpsa function for c1ccccc1C(=O)NC can be computed as:
Select sum(atom_psa*oechem.count_matches('c1ccccc1C(=O)NC',smarts)) from gnova.tpsa;
The gnova.tpsa function simply uses the SQL $1 parameter in place of 'c1ccccc1C(=O)NC' above.
Structural fingerprints can be produced as a bit string with each bit representing the presence or absence of particular fragments or keys.^{2} We describe here how the CHORD SQL function matches and orsum can be used to implement this computation. The table gnova.public166keys contains 166 rows, each with a SMARTS representing a fragment, a brief description of the fragment and a bit number between 1 and 166. The following SQL statement:
Select * from gnova.public166keys where matches('c1ccccc1C(=O)NC',smarts);
will produce 14 rows detailing which fragments are contained in this structure. A single row containing just the bitstring with each of these bits set can be computed using:
Select orsum(bit_set(0::bit(166),bit)) from gnova.public166keys where matches('c1ccccc1C(=O)NC',smarts);
The general public166keys function is simply the above statment, with the SQL parameter $1 substituted in place of 'c1ccccc1C(=O)NC'.
LogP can be estimated in a similar fashion as a sum of atom based fragment values, using the method of Ghose and Krippen^{35}. Andrews' binding energy^{6} can also be computed as a sum of atom based fragment values. We have not implemented functions for these. If you have implemented any of these or other fragment based methods and wish to contribute them for other users, please let us know and we'll be glad to include them in future distributions of CHORD.