15.7.4.1 CREATE FUNCTION Statement for Loadable Functions (original) (raw)

15.7.4.1 CREATE FUNCTION Statement for Loadable Functions

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

This statement loads the loadable function named_functionname_. (CREATE FUNCTION is also used to created stored functions; seeSection 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”.)

A loadable function is a way to extend MySQL with a new function that works like a native (built-in) MySQL function such asABS() orCONCAT(). SeeAdding a Loadable Function.

functionname is the name that should be used in SQL statements to invoke the function. TheRETURNS clause indicates the type of the function's return value. DECIMAL is a legal value after RETURNS, but currentlyDECIMAL functions return string values and should be written like STRING functions.

IF NOT EXISTS prevents an error from occurring if there already exists a loadable function with the same name. It does not prevent an error from occurring if there already exists a built-in function having the same name. IF NOT EXISTS is also supported for CREATE FUNCTION statements. SeeFunction Name Resolution.

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

sharedlibraryname 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 theplugin_dir system variable. For more information, see Section 7.7.1, “Installing and Uninstalling Loadable Functions”.

CREATE FUNCTION requires theINSERT privilege for themysql system schema because it adds a row to the mysql.func system table to register the function.

CREATE FUNCTION also adds the function to the Performance Schema user_defined_functions table that provides runtime information about installed loadable functions. SeeSection 29.12.22.10, “The user_defined_functions Table”.

Note

Like the mysql.func system table, the Performance Schemauser_defined_functions table lists loadable functions installed usingCREATE FUNCTION. Unlike the mysql.func table, the user_defined_functions table also lists loadable functions installed automatically by server components or plugins. This difference makesuser_defined_functions preferable to mysql.func for checking which loadable functions are installed.

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

Note

To upgrade the shared library associated with a loadable function, issue aDROP FUNCTION statement, upgrade the shared library, and then issue aCREATE FUNCTION statement. If you upgrade the shared library first and then useDROP FUNCTION, the server may unexpectedly shut down.