Syntax\n------\n\nCREATE [OR REPLACE]\n    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]\n    [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])\n    RETURNS type\n    [characteristic ...]\n    func_body\n\nfunc_parameter:\n    [ OUT | INOUT | IN OUT ] param_name type |\n    [ IN ] param_name type [DEFAULT value or expression]\n\ntype:\n    Any valid MariaDB data type\n\ncharacteristic:\n    LANGUAGE SQL\n  | [NOT] DETERMINISTIC\n  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }\n  | SQL SECURITY { DEFINER | INVOKER }\n  | COMMENT 'string'\n\nfunc_body:\n    Valid SQL procedure statement\n\nDescription\n-----------\n\nUse the CREATE FUNCTION statement to create a new stored 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.\n\nYou 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.\n\nYou 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.\n\nYou 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.\n\nBy default, a function is associated with the current 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.\n\nThe 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.\n\nEach parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used.\n\nFor valid identifiers to use as function names, see Identifier Names.\n\nRETURN\n\nThe RETURN clause can return a function body. In newer versions of MariaDB, it can also return a cursor (using SYS_REFCURSOR).\n\nRETURN func_body\n\nThe RETURN clause can return a function body (a valid SQL PROCEDURE statement).\n\nRETURN SYS_REFCURSOR\n\nIt can also return a cursor, if the function was declared with a SYS_REFCURSOR variable, like this:\n\n``sql\nCREATE FUNCTION f1 RETURN SYS_REFCURSOR AS\n  c SYS_REFCURSOR;\nBEGIN\n  OPEN c FOR SELECT a FROM t1 ORDER BY a;\n  RETURN c;\nEND;\n/\n`\n\nAlternatively, a cursor can be returned in an OUT parameter, see this section.\n\nRETURN func_body\n\nThe RETURN clause can return a function body (a valid SQL PROCEDURE statement).\n\nAGGREGATE\n\nIt is possible to create stored aggregate functions as well. See Stored Aggregate Functions for details.\n\nIN | OUT | INOUT | IN OUT\n\nOUT, INOUT and its equivalent IN OUT, are only valid if called from SET and not SELECT. These quantifiers are especially useful for creating functions with more than one return value. This allows functions to be more complex and nested.\n\n`sql\nDELIMITER $$\nCREATE FUNCTION add_func3(IN a INT, IN b INT, OUT c INT) RETURNS INT\nBEGIN\n  SET c = 100;\n  RETURN a + b;\nEND;\n$$\nDELIMITER ;\n \nSET @a = 2;\nSET @b = 3;\nSET @c = 0;\nSET @res= add_func3(@a, @b, @c);\n\nSELECT add_func3(@a, @b, @c);\nERROR 4186 (HY000): OUT or INOUT argument 3 for function add_func3 is not allowed here\n\nDELIMITER $$\nCREATE FUNCTION add_func4(IN a INT, IN b INT, d INT) RETURNS INT\nBEGIN\n  DECLARE c, res INT;\n  SET res = add_func3(a, b, c) + d;\n  if (c > 99) then\n    return  3;\n  else\n    return res;\n  end if;\nEND;\n$$\n\nDELIMITER ;\n\nSELECT add_func4(1,2,3);\n+------------------+\n| add_func4(1,2,3) |\n+------------------+\n|                3 |\n+------------------+\n`\n\nQuantifiers are not available.\n\nA cursor can be returned, like this:\n\n`sql\nCREATE TABLE t1 (a INT);\nINSERT INTO t1 VALUES (10),(20);\n \nCREATE FUNCTION f1 RETURN SYS_REFCURSOR AS\n  c SYS_REFCURSOR;\nBEGIN\n  OPEN c FOR SELECT a FROM t1 ORDER BY a;\n  RETURN c;\nEND;\n/\n \nCREATE PROCEDURE p1 AS\n  a INT;\n  c SYS_REFCURSOR DEFAULT f1();\nBEGIN\n  LOOP\n    FETCH c INTO a;\n    EXIT WHEN c%NOTFOUND;\n    dbms_output.put_line(a);\n  END LOOP;\n  CLOSE c;\n  END;\n/\n \nCALL p1();\n`\n\nThis feature is not available.\n\nRETURNS\n\nThe RETURNS clause specifies the return type of the function. NULL values are permitted with all return types.\n\nWhat happens if the RETURN clause returns a value of a different type? It depends on the SQL_MODE in effect at the moment of the function creation.\n\nIf the SQL_MODE is strict (STRICT_ALL_TABLES or STRICT_TRANS_TABLES flags are specified), a 1366 error will be produced.\n\nOtherwise, 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.\n\nMariaDB 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.\n\nLANGUAGE SQL\n\nLANGUAGE SQL is a standard SQL clause which can be used in MariaDB for portability. However that clause has no meaning, because SQL is the only supported language for stored functions.\n\nA function is deterministic if it can produce only one result for a given list of parameters. If the result may be affected by stored data, server variables, random numbers or any value that is not explicitly passed, then the function is not deterministic. Also, a function is non-deterministic if it uses nondeterministic functions like NOW() or CURRENT_TIMESTAMP(). The optimizer may choose a faster execution plan if it known that the function is deterministic. In such cases, you should declare the routine using the DETERMINISTIC keyword. If you want to explicitly state that the function is not deterministic (which is the default) you can use the NOT DETERMINISTIC keywords.\n\nIf you declare a non-deterministic function as DETERMINISTIC, you may get incorrect results. If you declare a deterministic function as NOT DETERMINISTIC, in some cases the queries will be slower.\n\nOR REPLACE\n\nIf the optional OR REPLACE clause is used, it acts as a shortcut for:\n\n`sql\nDROP FUNCTION IF EXISTS function_name;\nCREATE FUNCTION function_name ...;\n`\n\nwith the exception that any existing privileges for the function are not dropped.\n\nIF NOT EXISTS\n\nIf the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the function already exists. Cannot be used together with OR REPLACE.\n\n\\[NOT] DETERMINISTIC\n\nThe [NOT] DETERMINISTIC clause also affects binary logging, because the STATEMENT format can not be used to store or replicate non-deterministic statements.\n\nCONTAINS SQL, NO SQL, READS SQL DATA, and MODIFIES SQL DATA are informative clauses that tell the server what the function does. MariaDB does not check in any way whether the specified clause is correct. If none of these clauses are specified, CONTAINS SQL is used by default.\n\nMODIFIES SQL DATA\n\nMODIFIES SQL DATA means that the function contains statements that may modify data stored in databases. This happens if the function contains statements like DELETE, UPDATE, INSERT, REPLACE or DDL.\n\nREADS SQL DATA\n\nREADS SQL DATA means that the function reads data stored in databases, but does not modify any data. This happens if SELECT statements are used, but there no write operations are executed.\n\nCONTAINS SQL\n\nCONTAINS SQL means that the function contains at least one SQL statement, but it does not read or write any data stored in a database. Examples include SET or DO.\n\nNO SQL\n\nNO SQL means nothing, because MariaDB does not currently support any language other than SQL.\n\nOracle Mode\n\nA subset of Oracle's PL/SQL language is supported in addition to the traditional SQL/PSM-based MariaDB syntax. See Oracle mode for details on changes when running Oracle mode.\n\nSecurity\n\nYou 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.\n\nEach function has an account associated as the definer. By default, the definer is the account\\\nthat created the function. Use the DEFINER clause to specify a different account as the\\\ndefiner. You must have the SET USER privilege to use the DEFINER clause. See Account Names for details on specifying accounts.\n\nThe 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.\n\nThis 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.\n\n`sql\nCREATE TABLE employees (name TINYTEXT, dept TINYTEXT, salary INT);\nGRANT SELECT (name, dept) ON employees TO roger;\n`\n\nTo allow the user the get the maximum salary for a department, define a function and grant the EXECUTE privilege:\n\n`sql\nCREATE FUNCTION max_salary (dept TINYTEXT) RETURNS INT RETURN\n  (SELECT MAX(salary) FROM employees WHERE employees.dept = dept);\nGRANT EXECUTE ON FUNCTION max_salary TO roger;\n`\n\nSince 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.\n\nCharacter Sets and Collations\n\nFunction return types can be declared to use any valid character set and collation. If used, the COLLATE attribute needs to be preceded by a CHARACTER SET` attribute.\n\nIf the character set and collation are not specifically set in the statement, the database defaults at the time of creation will be used. If the database defaults change at a later stage, the stored function character set/collation will not be changed at the same time; the stored function needs to be dropped and recreated to ensure the same character set/collation as the database is used.\n\nExamples\n--------\n\nCREATE FUNCTION hello (s CHAR(20))\n    RETURNS CHAR(50) DETERMINISTIC\n    RETURN CONCAT('Hello, ',s,'!');\n\nSELECT hello('world');\n+----------------+\n| hello('world') |\n+----------------+\n| Hello, world!  |\n+----------------+\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-function