27.3.2. CREATE FUNCTION Syntax
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}
SONAME shared_library_name
A user-defined function (UDF) is a way to extend MySQL with a
new function that works like a native (built-in) MySQL function
such as ABS() or CONCAT().
function_name is the name that should
be used in SQL statements to invoke the function. The
RETURNS clause indicates the type of the
function's return value. DECIMAL is a legal
value after RETURNS, but currently
DECIMAL functions return string values and
should be written like STRING functions.
shared_library_name is the basename
of the shared object file that contains the code that implements
the function. The file must be located in the plugin directory.
This directory is given by the value of the
plugin_dir system variable.
(Note: This a change in MySQL
5.1. For earlier versions of MySQL, the shared object can be
located in any directory that is searched by your system's
dynamic linker.)
To create a function, you must have the
INSERT and privilege for the
mysql database. This is necessary because
CREATE FUNCTION adds a row to the
mysql.func system table that records the
function's name, type, and shared library name. If you do not
have this table, you should run the
mysql_upgrade command to create it. See
Section 5.5.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.
An active function is one that has been loaded with
CREATE FUNCTION and not removed with
DROP FUNCTION. All active functions are
reloaded each time the server starts, unless you start
mysqld with the
--skip-grant-tables option. In this case, UDF
initialization is skipped and UDFs are unavailable.
For instructions on writing user-defined functions, see
Section 27.3.4, “Adding a New User-Defined Function”. For the UDF mechanism to work,
functions must be written in C or C++, your operating system
must support dynamic loading and you must have compiled
mysqld dynamically (not statically).
An AGGREGATE function works exactly like a
native MySQL aggregate (summary) function such as
SUM or COUNT(). For
AGGREGATE to work, your
mysql.func table must contain a
type column. If your
mysql.func table does not have this column,
you should run the mysql_upgrade script to
create it (see Section 5.5.2, “mysql_upgrade — Check Tables for MySQL Upgrade”).
