User Defined Functions

Concept

A user defined function returns a value and can be used anywhere an expression can - in a select list, a WHERE clause, a CHECK constraint. That is the whole difference from a procedure.

Syntax

DELIMITER $$

CREATE FUNCTION function_name(param1 type, param2 type)
RETURNS return_type
DETERMINISTIC                 -- or NOT DETERMINISTIC
READS SQL DATA                -- or NO SQL / MODIFIES SQL DATA
BEGIN
    DECLARE v_result return_type;
    -- ...
    RETURN v_result;
END$$

DELIMITER ;

Example

DELIMITER $$

CREATE FUNCTION years_of_service(p_hire_date DATE)
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_hire_date, CURDATE());
END$$

CREATE FUNCTION salary_band(p_salary DECIMAL(10,2))
RETURNS VARCHAR(10)
DETERMINISTIC
NO SQL
BEGIN
    RETURN CASE WHEN p_salary >= 100000 THEN 'senior'
                WHEN p_salary >=  70000 THEN 'mid'
                ELSE                         'junior'
           END;
END$$

DELIMITER ;

SELECT first_name,
       hire_date,
       years_of_service(hire_date) AS service_years,
       salary_band(salary)         AS band
FROM   employees
ORDER BY service_years DESC;

Explanation

The value of a function is consistency: salary_band encodes one definition of the bands, used by every report. Change the thresholds once and every consumer follows. Copy pasted CASE expressions drift apart within months.

The characteristics matter

Strictly, years_of_service is not deterministic, because it depends on CURDATE() - the same hire date gives a different answer next year. Declaring it DETERMINISTIC is a common and genuine bug: MySQL may cache the result, and statement based replication can produce different values on the replica.

The performance trap

-- Slow: the function runs once PER ROW, and the index on hire_date is unusable
SELECT * FROM employees WHERE years_of_service(hire_date) > 5;

-- Fast: a plain range on the bare column
SELECT * FROM employees WHERE hire_date <= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);

This is the same sargability rule as any other function, with an extra cost: a user defined function is opaque to the optimiser, which cannot see inside it to estimate anything. A scalar function that itself queries a table, used in a WHERE clause over a million rows, executes a million queries. It is one of the most reliable ways to make a fast database slow.

Table valued functions

-- PostgreSQL: a function returning a set, usable in FROM
CREATE FUNCTION orders_for_customer(p_id INT)
RETURNS TABLE (order_id INT, order_date DATE, total NUMERIC) AS $$
    SELECT id, order_date, total FROM orders WHERE customer_id = p_id;
$$ LANGUAGE sql STABLE;

SELECT * FROM orders_for_customer(1);

PostgreSQL, SQL Server and Oracle all support table valued functions. MySQL and MariaDB do not - a MySQL function returns a scalar only. Use a view or a procedure returning a result set instead.

Important rules

• A function must return a value on every path.
• MySQL functions cannot return a table; use a view or a procedure.
• Functions in a WHERE clause on an indexed column prevent index seeks.
• Declaring a non deterministic function as DETERMINISTIC risks wrong results and replication drift.
• MySQL requires SUPER privilege or log_bin_trust_function_creators to create functions when binary logging is on.
• SQLite has no CREATE FUNCTION; functions are registered by the host application.

Common mistakes

• Marking a function DETERMINISTIC because MySQL demands a characteristic and it is the shortest word.
• Calling a data reading function per row in a large query.
• Using a function to hide a join, and hiding the cost with it.
• Expecting MySQL to support table valued functions.
• Putting business rules in functions that no test covers.

Best practices

• Keep functions pure: inputs in, value out, no table access where you can avoid it.
• Declare determinism honestly.
• Use functions in the select list; keep them out of WHERE on large tables.
• Prefer a view or a CTE to a function that wraps a query.
• Version control every function definition alongside the schema.

Practice

1. Write a function returning the line value of an order item, and use it in a select list.
2. Explain why WHERE salary_band(salary) = 'senior' is slower than WHERE salary >= 100000.
3. Which of your functions are genuinely deterministic, and which only look it?