Mariadb Create Function

CREATE FUNCTION in MariaDB

Last updated: 2026-07-20

This skill covers the delta between standard SQL stored functions and MariaDB's three CREATE FUNCTION forms: (1) ordinary stored functions, (2) stored aggregate functions (CREATE AGGREGATE FUNCTION), and (3) loadable UDFs (CREATE FUNCTION ... SONAME) — a mechanism unrelated to stored routines that loads a compiled shared library. These three share a keyword but almost nothing else; do not conflate them.

Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Features available across current LTS branches (10.6, 10.11, 11.4, 11.8) are shown without a "since" annotation; only newer-than-10.6 features carry one.

What LLMs Often Miss

Stored Functions

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE}]
    FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN expr | func_body   -- func_body: BEGIN...END containing a RETURN

• func_parameter: [IN | OUT | INOUT | IN OUT] param_name type [DEFAULT value]. IN is the default direction. DEFAULT on a parameter is a MariaDB extension, not standard SQL.
• RETURNS is the declaration (the type, stated once, in the header); RETURN is the statement that produces a value and exits — either a bare expression (RETURN expr;) or inside a BEGIN...END compound body, which must contain at least one RETURN.
• characteristic: LANGUAGE SQL (accepted, no effect — SQL is the only language) | [NOT] DETERMINISTIC | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} (informational only, unchecked; defaults to CONTAINS SQL if omitted) | SQL SECURITY {DEFINER | INVOKER} (defaults to DEFINER) | COMMENT 'string'.
• Requires the CREATE ROUTINE privilege to create, EXECUTE to call; creation auto-grants EXECUTE/ALTER ROUTINE to the creator even when DEFINER names someone else.
• Stored-function-only restrictions (beyond the general stored-routine limitations): no statement that returns a result set (use SELECT ... INTO), no FLUSH, no explicit/implicit commit or rollback, no recursion, cannot modify a table already in use by the invoking statement, no prepared statements (PREPARE/EXECUTE/dynamic SQL).

CREATE FUNCTION hello(s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC
  RETURN CONCAT('Hello, ', s, '!');

Stored Aggregate Functions

CREATE [OR REPLACE] [DEFINER = ...] AGGREGATE FUNCTION [IF NOT EXISTS]
    func_name ([func_parameter[,...]]) RETURNS type
BEGIN
    -- declarations
    DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN return_val;
    LOOP
        FETCH GROUP NEXT ROW;   -- pulls the next row of the current group
        -- accumulate into local state
    END LOOP;
END;

• Everything about a plain stored function applies, plus: the AGGREGATE keyword, and FETCH GROUP NEXT ROW inside the loop — MariaDB's mechanism for driving the row-by-row accumulation. There is no separate "initialize/iterate/terminate" API to implement (unlike UDF aggregates below); it's one loop with a NOT FOUND handler that fires when the group is exhausted, from which you RETURN the accumulated result.
• Callable in GROUP BY queries and as a window function, exactly like a built-in aggregate.
• Oracle-mode (sql_mode=ORACLE) syntax is also supported, using RETURN (not RETURNS) in the header and an EXCEPTION WHEN NO_DATA_FOUND block instead of a CONTINUE HANDLER.

User-Defined (Loadable) Functions — CREATE FUNCTION ... SONAME

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

This is not a stored function — no body, no BEGIN...END, no RETURN. It registers a function implemented in a compiled shared library (.so/.dll), loaded from the plugin_dir directory. shared_library_name is a basename, not a path.

• Requires the INSERT privilege on the mysql database (INSERT + DELETE if OR REPLACE is used) — a different privilege model from stored routines' CREATE ROUTINE. Registration adds a row to mysql.func.
• RETURNS here is limited to four keywords: STRING, INTEGER, REAL, DECIMAL. DECIMAL UDFs are implemented at the C level the same way as STRING UDFs (no native C decimal type in the calling convention).
• AGGREGATE marks the UDF as an aggregate (summary) function, implemented via the UDF aggregate C API rather than FETCH GROUP NEXT ROW. Aggregate UDFs can also be used as window functions.
• Statements using UDFs are not safe for statement-based replication.
• Name-collision precedence: for an unqualified call, MariaDB resolves native built-ins first, then UDFs, then type constructors, then stored functions last — a UDF shadows a same-named stored function, not the other way around.
• To upgrade a UDF's library, DROP FUNCTION first, replace the library file, then CREATE FUNCTION again — swapping the library in place under a loaded UDF can crash the server.

See Also

• mariadb-create-procedure — shared stored-routine surface (parameters, characteristics, privileges) without the RETURNS/RETURN requirement
• mariadb-create-trigger — another routine body context governed by the same log_bin_trust_function_creators binlog-safety gate
• Canonical references on mariadb.com/docs (consult for edge cases not covered here):
  • https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-function
  • https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/stored-aggregate-functions
  • https://mariadb.com/docs/server/server-usage/user-defined-functions/create-function-udf

This page is: Copyright © 2026 MariaDB. All rights reserved.