top of page
CerebroSQL

MySQL: 

CREATE FUNCTION UDF

Syntax:
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 [HELP CREATE PROCEDURE].)

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
(https://dev.mysql.com/doc/extending-mysql/8.0/en/adding-udf.html).

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
https://dev.mysql.com/doc/refman/8.0/en/udf-loading.html.

CREATE FUNCTION requires the INSERT privilege for the mysql 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 UDFs. See
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-user-defined
-functions-table.html.

*Note*:

Like the mysql.func system table, the Performance Schema
user_defined_functions table lists UDFs installed using CREATE
FUNCTION. Unlike the mysql.func table, the user_defined_functions table
also lists UDFs installed automatically by server components or
plugins. This difference makes user_defined_functions preferable to
mysql.func for checking which UDFs are installed.

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.

URL: https://dev.mysql.com/doc/refman/8.0/en/create-function-udf.html

Example

bottom of page