skills.rest
mariadb-corporation
mariadb-corporationOFFICIAL
Explore all skills by this author →
WebsiteGitHub

mariadb-create-function

Guides writing MariaDB CREATE FUNCTION statements across stored, aggregate, and loadable UDF forms.

28|115|Updated Jan 28, 2025
Search Tags:#SQL#database#mariadb#udf#create function#stored function#aggregate function
One-click install
npx skills add https://github.com/mariadb-corporation/mariadb-docs --skill mariadb-create-function-mariadb-corporation
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: mariadb-create-function
Source: https://github.com/mariadb-corporation/mariadb-docs/tree/main/agent-skills/granular/statements/mariadb-create-function
Command: npx skills add https://github.com/mariadb-corporation/mariadb-docs --skill mariadb-create-function-mariadb-corporation

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? MariaDB has three distinct CREATE FUNCTION forms — stored functions, stored aggregate functions, and loadable user-defined functions — that share a keyword but differ in syntax, privileges, and behavior, and AI-generated SQL frequently conflates them or misses MariaDB-specific rules like the NOT DETERMINISTIC default and binlog creation gates. ## Core Features & Use Cases - Stored Function Guidance: Covers RETURNS vs RETURN, IN/OUT/INOUT parameters with DEFAULT values, characteristics like DETERMINISTIC and SQL SECURITY, and restrictions such as no result sets, no recursion, and no dynamic SQL. - Stored Aggregate Functions: Explains CREATE AGGREGATE FUNCTION with FETCH GROUP NEXT ROW loops and NOT FOUND handlers for custom aggregates usable in GROUP BY and window queries. - Loadable UDF Registration: Details CREATE FUNCTION ... SONAME for shared libraries loaded from plugin_dir, the mysql.func privilege model, and name-collision precedence where UDFs shadow same-named stored functions. - Use Case: When asked to write a custom aggregate in MariaDB, produce a CREATE AGGREGATE FUNCTION with a FETCH GROUP NEXT ROW loop instead of an incorrect scalar function plus GROUP BY workaround. ## Quick Start Ask the AI to write or review a MariaDB CREATE FUNCTION statement, custom aggregate, or UDF registration for your specific use case.

Frequently Asked Questions about mariadb-create-function

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I create a stored function in MariaDB?▼

Use CREATE FUNCTION with a RETURNS type declaration in the header and a RETURN statement in the body, either as a bare expression or inside a BEGIN...END block. Add DETERMINISTIC and a data-access characteristic like READS SQL DATA if the function must be created on a server with binary logging enabled.

How to write a custom aggregate function in MariaDB?▼

Use CREATE AGGREGATE FUNCTION with a LOOP containing FETCH GROUP NEXT ROW to pull each row of the current group, plus a DECLARE CONTINUE HANDLER FOR NOT FOUND that returns the accumulated result. The aggregate works in GROUP BY queries and as a window function.

What is the difference between CREATE FUNCTION SONAME and a stored function?▼

CREATE FUNCTION ... SONAME registers a loadable UDF backed by a compiled shared library in plugin_dir, with no SQL body and RETURNS limited to STRING, INTEGER, REAL, or DECIMAL. It requires INSERT privilege on the mysql database rather than CREATE ROUTINE, and is recorded in mysql.func.

Why does MariaDB reject my CREATE FUNCTION with binary logging enabled?▼

With --log-bin on and log_bin_trust_function_creators=OFF, creating any stored function requires a SUPER-class privilege, and a non-deterministic function declared as CONTAINS SQL or MODIFIES SQL DATA is rejected outright. Set log_bin_trust_function_creators=ON or declare the function DETERMINISTIC if accurate.

Can MariaDB stored functions have OUT or INOUT parameters?▼

Yes, since MariaDB 10.8 stored functions accept OUT and INOUT parameters, but they can only be supplied when the function is called from a SET assignment. Calling with an OUT or INOUT argument from a SELECT raises error 4186.

Which wins when a UDF and stored function share the same name in MariaDB?▼

For unqualified calls, MariaDB resolves native built-ins first, then UDFs, then type constructors, and stored functions last. A UDF named foo always shadows a same-named stored function for unqualified invocations.

Related Skills

Explore More Software Engineering Skills→
Percona-LabPercona-Lab
1

mysql-js-stored-procedures

Write JavaScript stored procedures and functions in Percona Server for MySQL.

Official
Intermediate
likweitanlikweitan
49

abap-sql-amdp

Write modern ABAP SQL and AMDP procedures for ABAP Cloud and standard ABAP.

Community
Advanced
avvaleavvale

aurora-tools-scripts

Manage PostgreSQL SQL migrations and stored procedures within the tools bounded context.

Official
Intermediate
yunqiqiliangyunqiqiliang
8

clickzetta-external-function

Register external functions and UDFs for SQL invocation via cloud providers.

Community
Advanced