Function plpgsql

Creating a function in plpgsql is done in a way similar to the previous examples using the SQL language. The following function creation shows some of the useful features of plpgsql and differences from the SQL language. 

The smiles to symbols and smile to bonds functions return arrays of values. In the sample output above, the smiles to bonds output has been truncated for easier viewing. Some client programs may expect this information as separate rows, as if they were records in a file. These arrays may be cast into that form by using a plpgsql function that returns elements of an array as rows. This is shown in the next section. 

If SMILES is used to store molecular structures in a relational database management system(RDBMS), it may be necessary to extract the symbol and bond information for some client programs that expect a connection table. The smiles to symbol and smiles to bonds function shown in the next sections allow the symbol and bond information in a SMILES to be extracted as an array. Some client programs may prefer to process this information in rows, as if they were records in a file. The following plpgsql functions can be used to present the array elements as rows. Two functions are shown ctable (connection table) and symbol coords. The symbol coords function requires an array of coordinates in addition to the symbols. 

In functions written in other languages, the code appears in the create command, whether it is written in SQL, plpgsql, plperl, or plpython. For C language functions, the name of the shared object, here bits.so, and the name of the c function, here nbits set are named in the create command. 

---