Stored Functions
Stored functions are a powerful SQL tool that allows you to create reusable code blocks for performing calculations and data transformations. Unlike built-in functions, stored functions are created by developers to solve specific tasks.
A stored function is a named block of SQL code that accepts parameters, performs calculations, and always returns a single value of a specific type.
General Structure of a Stored Function
MySQL 8.1CREATE FUNCTION function_name(parameter1 TYPE, parameter2 TYPE, ...) RETURNS return_type BEGIN -- function logic RETURN calculation_result; END;
MySQL 8.1CREATE OR REPLACE FUNCTION function_name(parameter1 TYPE, parameter2 TYPE, ...) RETURNS return_type LANGUAGE plpgsql AS $$ BEGIN -- function logic RETURN calculation_result; END; $$;
LANGUAGE plpgsql — specifies that the function is written in PL/pgSQL (PostgreSQL's procedural language).
AS $$ ... $$ — dollar quoting, a special way to delimit the function body. Allows you to avoid escaping characters inside the function.
Simple Function Example
Let's create a function to determine if a person is an adult based on their birth date:
MySQL 8.1CREATE FUNCTION is_adult(birth_date DATE) RETURNS BOOLEAN BEGIN RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18; END;
MySQL 8.1CREATE OR REPLACE FUNCTION is_adult(birth_date DATE) RETURNS BOOLEAN LANGUAGE plpgsql AS $$ BEGIN RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18; END; $$;
Now this function can be used in any query:
MySQL 8.1-- Create the function CREATE FUNCTION is_adult(birth_date DATE) RETURNS BOOLEAN BEGIN RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18; END; -- Use the function SELECT is_adult('2010-05-15') AS child_status, is_adult('2000-03-20') AS adult_status;
MySQL 8.1-- Create the function CREATE OR REPLACE FUNCTION is_adult(birth_date DATE) RETURNS BOOLEAN LANGUAGE plpgsql AS $$ BEGIN RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18; END; $$; -- Use the function SELECT is_adult('2010-05-15') AS child_status, is_adult('2000-03-20') AS adult_status;
Using Functions in Table Queries
Stored functions are especially useful when working with real data. For example, we can use our function to filter students by age:
MySQL 8.1-- Create the function CREATE FUNCTION is_adult(birth_date DATE) RETURNS BOOLEAN BEGIN RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18; END; -- Use the function in a table query SELECT first_name, last_name, birthday, is_adult(birthday) AS is_adult FROM Student WHERE is_adult(birthday) = TRUE LIMIT 5;
MySQL 8.1-- Create the function CREATE OR REPLACE FUNCTION is_adult(birth_date DATE) RETURNS BOOLEAN LANGUAGE plpgsql AS $$ BEGIN RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18; END; $$; -- Use the function in a table query SELECT first_name, last_name, birthday, is_adult(birthday) AS is_adult FROM Student WHERE is_adult(birthday) = TRUE LIMIT 5;
Functions with Database Queries
Stored functions can execute SQL queries inside themselves to retrieve necessary data:
MySQL 8.1CREATE FUNCTION get_student_lessons_count(student_id INT, target_date DATE) RETURNS INT BEGIN DECLARE lessons_count INT; SELECT COUNT(*) INTO lessons_count FROM Schedule s INNER JOIN Student_in_class sic ON s.class = sic.class WHERE sic.student = student_id AND s.date = target_date; RETURN lessons_count; END;
MySQL 8.1CREATE OR REPLACE FUNCTION get_student_lessons_count(student_id INT, target_date DATE) RETURNS INT LANGUAGE plpgsql AS $$ DECLARE lessons_count INT; BEGIN SELECT COUNT(*) INTO lessons_count FROM Schedule s INNER JOIN Student_in_class sic ON s.class = sic.class WHERE sic.student = student_id AND s.date = target_date; RETURN lessons_count; END; $$;
This function counts the number of lessons for a specific student on a given day:
MySQL 8.1SELECT get_student_lessons_count(1, '2019-09-01') AS lessons_today;
MySQL 8.1SELECT get_student_lessons_count(1, '2019-09-01') AS lessons_today;
Breaking Down the Example with Variables
Let's analyze the previous example step by step to understand how variables and the INTO construct work:
MySQL 8.1DECLARE lessons_count INT;
This line declares a variable lessons_count of type INT. The variable will store the result of our query.
Important for PostgreSQL: All variables must be declared in the DECLARE block before the function body starts (before BEGIN). You cannot declare variables inside the function body.
MySQL 8.1SELECT COUNT(*) INTO lessons_count FROM Schedule s INNER JOIN Student_in_class sic ON s.class = sic.class WHERE sic.student = student_id AND s.date = target_date;
Here we save the query result into a variable:
- SELECT COUNT(*) — counts the number of records
- INTO lessons_count — saves the result into the lessons_count variable
- The rest — a regular SQL query with JOIN and conditions
MySQL 8.1RETURN lessons_count;
Return the variable value as the function result.
Important: The INTO construct allows you to save the result of a SELECT query into a variable. This is the foundation of working with data inside stored functions.
Managing Stored Functions
-
Viewing Existing Functions
MySQL 8.1SHOW FUNCTION STATUS WHERE Db = 'your_database_name';
MySQL 8.1SELECT routine_name, routine_type FROM information_schema.routines WHERE routine_type = 'FUNCTION' AND routine_schema = 'public';
-
Dropping a Function
MySQL 8.1DROP FUNCTION IF EXISTS is_adult;
MySQL 8.1DROP FUNCTION IF EXISTS is_adult(DATE);
-
Modifying a Function
To modify a function in MySQL, you need to drop the old version first, then create a new one:
MySQL 8.1DROP FUNCTION IF EXISTS is_adult; -- Create new version of the function CREATE FUNCTION is_adult(birth_date DATE) ...
In PostgreSQL, you can use CREATE OR REPLACE FUNCTION:
MySQL 8.1CREATE OR REPLACE FUNCTION is_adult(birth_date DATE) RETURNS BOOLEAN -- new implementation
Stored functions are a powerful tool for creating reusable business logic directly in the database. They help centralize calculations and ensure data consistency across the entire application! 🚀