Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 29.0Mb
PDF (A4) - 29.0Mb
PDF (RPM) - 28.0Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 181.9Kb
Man Pages (Zip) - 291.8Kb
Info (Gzip) - 2.8Mb
Info (Zip) - 2.8Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  CREATE FUNCTION Statement for User-Defined Functions

13.7.3.1 CREATE FUNCTION Statement for User-Defined Functions

CREATE [AGGREGATE] FUNCTION function_name
    RETURNS {STRING|INTEGER|REAL|DECIMAL}
    SONAME shared_library_name

This statement loads the user-defined function (UDF) named function_name. (CREATE FUNCTION is also used to created stored functions; see Section 13.1.15, “CREATE PROCEDURE and CREATE FUNCTION Statements”.)

A user-defined function is a way to extend MySQL with a new function that works like a native (built-in) MySQL function such as ABS() or CONCAT(). See Adding a User-Defined Function.

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.

The AGGREGATE keyword, if given, signifies that the UDF is an aggregate (group) function. An aggregate UDF works exactly like a native MySQL aggregate function such as SUM() or COUNT().

shared_library_name is the base name of the shared library file containing 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.1, “Installing and Uninstalling User-Defined Functions”.

CREATE FUNCTION requires the INSERT privilege for the mysql system database because it adds a row to the mysql.func system table to register the function.

During the normal startup sequence, the server loads UDFs registered in the mysql.func table. If the server is started with the --skip-grant-tables option, UDFs registered in the table are not loaded and are unavailable.

Note

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 unexpectedly shut down.