PostgreSQL function

New SQL functions and data types can be used to extend a relational database. This is explained in Chapter 10 using PostgreSQL as an example. Ways in which three-dimensional molecular structures can be stored are examined in Chapter 11. This chapter also advocates using an RDBMS instead of molecular structure files and shows how this transition might be accomplished. 

Once this function is created, it can be used just like any standard SQL function. This function uses PostgreSQL syntax. 

Some of the more advanced methods described in this book require a more specific use of the RDBMS. The choice made for this book is PostgreSQL. In cases where a particular feature of PostgreSQL is used, a note is added to alert the reader. For example, the array data type in SQL2003 is implemented in PostgreSQL very differently than in Oracle. The list matches function described in a later chapter of this book returns an array of integers that denote which atoms in a structure match a substructure query. The integration of this function into SQL would be handled quite differently in PostgreSQL, Oracle and MySQL. 

PostgreSQL 8.2 documentation. 2008. C-language functions. http //www. postgresql.org/docs/8.2/static/xfunc-c.html (accessed April 21, 2008). 

PostgreSQL functions. 2008. http //us3.php.net/pgsql (accessed April 18, 2008). 

The definition of caspart is not shown here, but could be written in any language supported by PostgreSQL. The caspart function would parse the CAS number into each of its three integer parts. 

Here, the md5 function is a hash function available in PostgreSQL. It is used as a method to partition the logp values in the properties table into two arbitrary sets of about the same size. The less than operator ensures exactly two sets, and the use of the md5 function ensures that the sets are arbitrary and of about the same size. Note that using md5 results in arbitrary but not random sets. In other words, each time the select statements above are run, exactly the same sets will result, as long as no new rows are inserted. Rather than use this SQL statement every time the test set is desired, a test set view and training set view can be defined as ... 

The array upper function requires two arguments the name of the array and the index for which the upper limit is requested. The function call array upper (coord, 1) would return the number of atoms. The function call array upper (coord, 2) would return 3. Even though the second index upper limit was specified as 3 in the table creation above, this was done for clarity and because the array was intended to hold 3-D coordinates. PostgreSQL does not enforce this upper limit. In fact, it would be possible to insert two-dimensional coordinates into the coordtest table. However, it is not allowed to mix two- and three-dimensional coordinates within any one array. Once the first atoms coordinates are given the insert statement, each succeeding atom must have the same dimensionality of coordinates. 

It is possible to write PostgreSQL functions in C. Because PostgreSQL itself is written in C, any extension functions can take advantage of the internal representation of RDMBS data. In fact, examples on which C functions might be based are the very functions used by the PostgreSQL RDMBS for processing standard SQL data. This puts any C functions on an equal footing with any built-in SQL functions. For this reason, C functions are the best choice when issues of speed are important. 

There is an open-source extension of PostgreSQL called pgchem.7 It uses C functions and OpenBabel. It implements some of the core functions described in Chapter 7. The names of the functions are not the same as the names used here. Using pgchem, it should be possible to perform most of the operations represented by the core functions described in this book. 

The Appendix contains a simple C language function to return the number of bits set in a bit data type. The PostgreSQL Web site documentation contains examples of C language functions. 

PostgreSQL is generally referred to as an Object Relational Database Management System (ORDBMS). The use of the word object implies objects in the sense of an object-oriented computer language. While not intended to be fully object-oriented in the same sense as a computer language, an ORDBMS shares the essential aspects of objects. These include composite data types, methods (functions), and inheritance. 

This function is specific to PostgreSQL, using system catalog tables. It can be used in a Web application as in the following php example. 

This Appendix contains structured query language (SQL) functions and tables too large or complex for the explanatory nature of the earlier chapters. These functions and tables are practical, rather than explanatory. They all follow PostgreSQL syntax. Some of them require the core functions described in Chapter 7 of this book, for example, match, cansmiles, and count matches. Those functions are available in the CHORD product from gNova, Inc. This Appendix also contains a PerlMol implementation, a FROWNS implementation, and an OpenBabel implementation of the core functions for PostgreSQL. 

Several SQL functions are discussed in the earlier chapters. This section shows the code needed to define these functions and make them available for use in a PostgreSQL database. 

The following code will define the core functions described in Chapter 7 of this book. The isosmiles function is not included here because of limitation of PerlMol. These functions apply only to the PostgreSQL RDBMS. 

FROWNS python modules are used to show a second way in which the core functions might be implemented. The following plpythonu code extends PostgreSQL with most of the core functions described in Chapter 7. The isosmiles and keksmiles functions are not included here because of limitations of FROWNS. 

It is necessary to install these as the PostgreSQL superuser, here postgres, or as any other PostgreSQL superuser. This is because the "untrusted" language plpythonu is used. It might be more accurate to say "unrestricted," since plpythonu can use all the functionality of python. 

Suppose this code exists in a file bits.c. The following linux commands will process this file and make it useable as a new PostgreSQL function. 

---