Structured Query Language (SQL) - Design and Use of Relational Databases in Chemistry - page 27

the function. For example, the following SQL function converts pressure
in atmospheres to kilopascals.
Create Function convert.atm_to_kpa(Numeric) Returns Numeric
As 'Select $1 * 101.325;' Language SQL;
Once this function is created, it can be used just like any standard SQL
function. This function uses PostgreSQL syntax.
As with tables, functions are associated with a schema within the
database. In the above example, the schema named convert is used in
order to conveniently locate conversion functions in a common schema.
A function name can be fully qualified using its schema name, as in the
following example.
Select bp, convert.atm_to_kpa(bp_press) as "kPa" from epa.properties;
The result is a table of boiling points and the corresponding pressure con-
verted to kiloPascals from the pressure value stored in atmospheres in the
table epa.properties.
Of course, more elaborate functions could be written, using SQL or
one of the procedural languages available for the RDBMS being used. Plsql
or plpgsql allow all of the SQL commands as well as structured program-
ming constructs such as loops and if-then-else branching not available in
SQL itself. When using languages other than SQL, keep in mind that the
differences among various RDBMS is greater for the associated procedural
languages than for SQL proper. The appendix of this topic contains many
examples of SQL functions written using SQL, plpgsql, plpython, and C.
3.7.2 Aggregate Functions
Aggregate functions operate on a group of values rather than individ-
ual values as ordinary (or scalar) functions do. SQL has several standard
aggregate functions, for example, sum , average , and max . The following
SQL would likely return multiple rows.
Select ic50 From hiv_inh;
The following SQL would return just one row.
Select avg(ic50) From hiv_inh;
Another use of an aggregate function depends on the use of the Group
by clause of SQL. The following SQL will return multiple rows.
Select id, avg(ic50) From hiv_inh Group By id;