Database Reference
In-Depth Information
11.3.2 User-Defined Functions and Aggregates
When the built-in SQL functions are insufficient for a particular task or analysis,
SQL enables the user to create user-defined functions and aggregates. This custom
functionality can be incorporated into SQL queries in the same ways that the
built-in functions and aggregates are used. User-defined functions can also be
created to simplify processing tasks that a user may commonly encounter.
For example, a user-defined function can be written to translate text strings for
female (F) and male (M) to 0 and 1, respectively. Such a function may be helpful
when formatting data for use in a regression analysis. Such a function,
fm_convert()
, could be implemented as follows:
CREATE FUNCTION fm_convert(text) RETURNS integer AS
'SELECT CASE
WHEN $1 = ''F'' THEN 0
WHEN $1 = ''M'' THEN 1
ELSE NULL
END'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT
In declaring the function, the SQL query is placed within single quotes. The first
and only passed value is referenced by
$1
. The SQL query is followed by a
LANGUAGE
statement that explicitly states that the preceding statement is written
in SQL. Another option is to write the code in C.
IMMUTABLE
indicates that the
function does not update the database and does not use the database for lookups.
The
IMMUTABLE
declaration informs the database's query optimizer how best to
implement the function. The
RETURNS NULL ON NULL INPUT
statement
specifies how the function addresses the case when any of the inputs are
null
values.
In the online retail example, the
fm_convert()
function can be applied to the
customer_ gender
column in the
customer_demographics
table as follows.
SELECT customer_gender,
fm_convert(customer_gender) as male
FROM customer_demographics
LIMIT 5
