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.

General Structure of a Stored Function

CREATE FUNCTION function_name([parameter type [, ...]])
RETURNS return_type
BEGIN
    -- function logic
    RETURN result;
END;

CREATE OR REPLACE FUNCTION function_name([parameter type [, ...]])
RETURNS return_type
LANGUAGE plpgsql
AS $$
BEGIN
    -- function logic
    RETURN result;
END;
$$;

Simple Function Example

Let's create a function to determine if a person is an adult based on their birth date:

MySQL 8.4
CREATE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;
END;

MySQL 8.4
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;
$$;

Now this function can be used in any query:

MySQL 8.4
-- 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.4
-- 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.4
-- 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.4
-- 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.4
CREATE 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.4
CREATE 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.4
SELECT get_student_lessons_count(1, '2019-09-01') AS lessons_today;

MySQL 8.4
SELECT 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.4
DECLARE lessons_count INT;

This line declares a variable lessons_count of type INT. The variable will store the result of our query.

MySQL 8.4
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;

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.4
RETURN lessons_count;

Return the variable value as the function result.

Managing Stored Functions

• Viewing Existing Functions

  MySQL 8.4
  SHOW FUNCTION STATUS WHERE Db = 'your_database_name';
  

  MySQL 8.4
  SELECT routine_name, routine_type
  FROM information_schema.routines
  WHERE routine_type = 'FUNCTION' AND routine_schema = 'public';
  

• Dropping a Function

  MySQL 8.4
  DROP FUNCTION IF EXISTS is_adult;
  

  MySQL 8.4
  DROP 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.4
  DROP 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.4
  CREATE 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! 🚀