How to Create UDFs in MariaDB

Galaxy Glossary

How do I create and use user-defined functions in MariaDB?

A MariaDB UDF (user-defined function) lets you encapsulate reusable logic in SQL and call it like a built-in function.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What are UDFs in MariaDB?

User-defined functions wrap business logic into callable routines, returning a single value per row. They improve readability and reuse across queries that touch tables such as Orders or OrderItems.

How do you create a UDF?

Run CREATE FUNCTION with a name, input parameters, return type, and routine body. Mark deterministic functions and set the SQL level (e.g., READS SQL DATA) for optimal planning.

DELIMITER // CREATE FUNCTION total_after_tax(subtotal DECIMAL(10,2), tax_rate DECIMAL(5,2)) RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN RETURN subtotal + (subtotal * tax_rate/100); END // DELIMITER ;

Why specify DETERMINISTIC?

Deterministic tells the optimizer the function returns the same result for identical inputs, enabling better caching and parallelization.

How do you call a UDF in a query?

Invoke it like any built-in function:

SELECT id, total_after_tax(total_amount, 8) AS total_with_tax FROM Orders WHERE order_date >= '2024-01-01';

How can you alter or drop a UDF?

MariaDB lacks ALTER FUNCTION; instead, DROP FUNCTION name; and recreate with new logic. Use versioned names or deploy in maintenance windows.

Best practices for UDFs

Keep logic pure and side-effect-free, return scalar values only, document parameter units, and watch for data-type mismatches. Version control your .sql files to track changes.

Common pitfalls and fixes

NULL handling: Always coalesce nullable inputs to avoid unexpected NULL results. Privilege errors: GRANT EXECUTE ON FUNCTION to application roles before production use.

Why How to Create UDFs in MariaDB is important

How to Create UDFs in MariaDB Example Usage


-- List top customers by lifetime value
SELECT c.id, c.name, customer_clv(c.id) AS lifetime_value
FROM Customers AS c
ORDER BY lifetime_value DESC
LIMIT 10;

How to Create UDFs in MariaDB Syntax


DELIMITER //
CREATE FUNCTION function_name (param1 datatype [, paramN datatype])
RETURNS return_datatype
[DETERMINISTIC | NOT DETERMINISTIC]
[NO SQL | READS SQL DATA | MODIFIES SQL DATA]
[SQL SECURITY {DEFINER | INVOKER}]
COMMENT 'optional description'
BEGIN
    -- business logic
    RETURN expression;
END //
DELIMITER ;

-- E-commerce example: compute customer lifetime value (CLV)
DELIMITER //
CREATE FUNCTION customer_clv(p_customer_id INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC READS SQL DATA
BEGIN
  DECLARE clv DECIMAL(12,2);
  SELECT COALESCE(SUM(total_amount),0)
  INTO clv
  FROM Orders
  WHERE customer_id = p_customer_id;
  RETURN clv;
END //
DELIMITER ;

Common Mistakes

Omitting DETERMINISTIC for pure functions: This prevents the optimizer from caching results, causing needless re-execution. Always label predictable UDFs as DETERMINISTIC.
Returning the wrong data type: If the RETURN type is smaller than calculated values, MariaDB silently truncates. Match RETURN precision to the largest possible result.