13.7.3.1 CREATE FUNCTION Syntax for User-Defined Functions
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 base name of the shared library 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. For more information, see Section 5.6.2, “Installing and Uninstalling User-Defined Functions”.
To create a function, you must have the INSERT
privilege for the mysql
system 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.
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 28.4.2, “Adding a User-Defined Function”. For the UDF mechanism to work, functions must be written in C or C++ (or another language that can use C calling conventions), 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()
.
To upgrade the shared library associated with a UDF, issue a DROP FUNCTION
statement, upgrade the shared library, and then issue a CREATE FUNCTION
statement. If you upgrade the shared library first and then use DROP FUNCTION
, the server may crash.