CREATE FUNCTION

Syntax:

CREATE
    [DEFINER = {user | CURRENT_USER}]
    FUNCTION func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body
func_parameter:
    param_name type
type:
    Any valid MySQL data type
characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'
func_body:
    Valid SQL procedure statement

Description:

Use the CREATE FUNCTION statement to create a new function. You must have the CREATE ROUTINE database privilege to use CREATE FUNCTION. A function takes any number of arguments and returns a value from the function body. The function body can be any valid SQL expression as you would use, for example, in any select expression. If you have the appropriate privileges, you can call the function exactly as you would any built-in function. See Security below for details on privileges.

You can also use a variant of the CREATE FUNCTION statement to install a user-defined function (UDF) defined by a plugin. See CREATE FUNCTION (UDF) for details.

You can use a SELECT statement for the function body by enclosing it in parentheses, exactly as you would to use a subselect for any other expression. The SELECT statement must return a single value. If more than one column is returned when the function is called, error 1241 results. If more than one row is returned when the function is called, error 1242 results. Use a LIMIT clause to ensure only one row is returned.

You can also replace the RETURN clause with a BEGIN...END# compound statement. The compound statement must contain a RETURN statement. When the function is called, the RETURN statement immediately returns its result, and any statements after RETURN are effectively ignored.

By default, a function is associated with the default database. To associate the function explicitly with a given database, specify the fully-qualified name as db_name.func_name when you create it. If the function name is the same as the name of a built-in function, you must use the fully qualified name when you call it.

The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive.

Each parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used.

The RETURNS clause specifies the return type of the function. If the RETURN clause returns a value of a different type, the value is coerced to the proper type. For example, if a function specifies an ENUM or SET value in the RETURNS clause, but the RETURN clause returns an integer, the value returned from the function is the string for the corresponding ENUM member of set of SET members.

MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force, regardless of the server SQL mode in effect when the routine is invoked.

Security

You must have the EXECUTE privilege on a function to call it. MariaDB automatically grants the EXECUTE and ALTER ROUTINE privileges to the account that called CREATE FUNCTION, even if the DEFINER clause was used.

Each function has an account associated as the definer. By default, the definer is the account that created the function. Use the DEFINER clause to specify a different account as the definer. You must have the SUPER privilege to use the DEFINER clause. See Account Names for details on specifying accounts.

The SQL SECURITY clause specifies what privileges are used when a function is called. If SQL SECURITY is INVOKER, the function body will be evaluated using the privileges of the user calling the function. If SQL SECURITY is DEFINER, the function body is always evaluated using the privileges of the definer account. DEFINER is the default.

This allows you to create functions that grant limited access to certain data. For example, say you have a table that stores some employee information, and that you've granted SELECT privileges only on certain columns to the user account roger.

CREATE TABLE employees (name TINYTEXT, dept TINYTEXT, salary INT);
GRANT SELECT (name, dept) ON employees TO roger;

To allow the user the get the maximum salary for a department, define a function and grant the EXECUTE privilege:

CREATE FUNCTION max_salary (dept TINYTEXT) RETURNS INT RETURN
  (SELECT MAX(salary) FROM employees WHERE employees.dept = dept);
GRANT EXECUTE ON FUNCTION max_salary TO roger;

Since SQL SECURITY defaults to DEFINER, whenever the user roger calls this function, the subselect will execute with your privileges. As long as you have privileges to select the salary of each employee, the caller of the function will be able to get the maximum salary for each department without being able to see individual salaries.

Examples

The following example function takes a parameter, performs an operation using an SQL function, and returns the result.

MariaDB [test]> CREATE FUNCTION hello (s CHAR(20))
    -> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> 

You can use a compound statement in a function to manipulate data with statements like INSERT and SELECT. The following example creates a counter function that uses a temporary table to store the current value. Because the compound statement contains statements terminated with semicolons, you have to first change the statement delimiter with the DELIMITER statement to allow the semicolon to be used in the function body.

CREATE TEMPORARY TABLE counter (c INT);
INSERT INTO counter VALUES (0);
DELIMITER //
CREATE FUNCTION counter () RETURNS INT
  BEGIN
    UPDATE counter SET c = c + 1;
    RETURN (SELECT c FROM counter LIMIT 1);
  END //
DELIMITER ;