Создание простых функций: CREATE FUNCTION

В PostgreSQL функции — это мощный инструмент, позволяющий автоматизировать задачи, создавать бизнес-логику и предлагать серверу больше интеллектуальности. Представьте себе функции как мини-программы, которые выполняются внутри базы данных. Они удобны для:

• Повторного использования кода. Если вы повторяете одни и те же запросы по несколько раз, оберните их в функцию и вызывайте, когда нужно.
• Автоматизации задач. Например, вам нужно вычислить зарплату сотрудников на основе их часов работы. Функция справится с этим на ура.
• Инкапсуляции логики. Позволяет оставить сложные вычисления на стороне сервера, чтобы клиенты не ломали голову над SQL-запросами.

Общий синтаксис CREATE FUNCTION

Вот как выглядит общая структура для создания функции:

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
    -- Тело функции (логика)
    RETURN результат;
END;
$$ LANGUAGE plpgsql;

Разберем основные части:

CREATE FUNCTION function_name(parameters):

В этой строке мы задаем имя функции function_name и указываем параметры (если нужны).

Параметры могут содержать имя и тип данных: my_param INTEGER, another_param TEXT.

RETURNS return_type:

Указываем, что будет возвращать наша функция: одно значение (INTEGER, TEXT и т.д.) или набор данных (TABLE, RECORD).

BEGIN ... END:

Внутри этих ключевых слов находится "тело" функции, где происходит вся магия.

RETURN результат:

Возвращает результат выполнения функции. Будьте аккуратны: тип результата должен соответствовать тому, что вы указали в RETURNS.

LANGUAGE plpgsql:

Указываем, что используем язык PL/pgSQL. PostgreSQL поддерживает и другие языки, но сейчас нам нужен именно этот.

Простой пример: сложение двух чисел

Давайте создадим функцию, которая возвращает сумму двух целых чисел.

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Теперь вызовем ее:

SELECT add_numbers(5, 7); -- Результат: 12

Что здесь происходит?

• Функция принимает два параметра a и b типа INT.
• Внутри функции мы просто складываем их (a + b) и возвращаем результат.
• Всё просто, как калькулятор!

Пример с использованием использования переменных

Представим, что у нас есть база данных университета и мы хотим узнать, сколько студентов зарегистрировано.

Создадим функцию:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT; -- Объявляем переменную для хранения результата
BEGIN
    SELECT COUNT(*) INTO total FROM students; -- Считаем количество строк в таблице
    RETURN total; -- Возвращаем результат
END;
$$ LANGUAGE plpgsql;

Вызов функции:

SELECT count_students(); -- Предположим, результат: 120

Здесь мы видим:

• Использование переменной total для хранения результата SQL-запроса.
• Команда SELECT ... INTO записывает результат выполнения запроса в переменную.

Такой подход особенно удобен, если вам нужно сначала обработать данные, а затем их вернуть.

Возвращение нескольких значений: RETURNS TABLE

В предыдущем примере мы возвращали только одно значение. Но что если наша функция должна вернуть набор данных, например, список студентов? Именно здесь RETURNS TABLE становится полезным.

Пример:

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students;
END;
$$ LANGUAGE plpgsql;

Вызов функции:

SELECT * FROM get_students();

Возможный езультат:

Польза RETURN QUERY для выполнения запросов внутри функции

RETURN QUERY позволяет нам возвратить результат выполнения SQL-запроса напрямую из функции. Это сокращает промежуточные шаги и делает функции проще.

Создадим функцию, которая возвращает только тех студентов, которые активны:

CREATE FUNCTION get_active_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students WHERE active = TRUE;
END;
$$ LANGUAGE plpgsql;

Прежде чем вызывать функцию get_active_students(), нужно создать таблицу students и заполнить её тестовыми данными. Вот как это можно сделать:

-- Создаём таблицу студентов
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    active BOOLEAN DEFAULT TRUE
);

-- Добавляем несколько записей
INSERT INTO students (name, active) VALUES
('Alice', FALSE),
('Bob', TRUE),
('Charlie', TRUE),
('Dana', FALSE);

Таблица:

Теперь вызов:

SELECT * FROM get_active_students();

Результат:

Проверка корректности данных перед выполнением

Функции могут содержать проверки IF, чтобы гарантировать, что данные корректны. Например, мы можем создать функцию для перевода студента на следующий курс только если он сдал все экзамены.

Пример:

CREATE FUNCTION promote_student(student_id INT) RETURNS TEXT AS $$
DECLARE
    passed_exams INT;
BEGIN
    -- Считаем количество сданных экзаменов студента
    SELECT COUNT(*) INTO passed_exams
    FROM exams
    WHERE student_id = promote_student.student_id AND status = 'passed';

    -- Проверяем условие
    IF passed_exams < 5 THEN
        RETURN 'Student has not passed enough exams';
    END IF;

    -- Обновляем курс студента
    UPDATE students
    SET course = course + 1
    WHERE id = promote_student.student_id;

    RETURN 'Student promoted!';
END;
$$ LANGUAGE plpgsql;

Типичные ошибки при создании функций

Отсутствие типа результата. PostgreSQL всегда требует, чтобы вы указали, какой результат вернёт функция. Например:

CREATE FUNCTION fail() AS $$ -- Ошибка: нет RETURNS
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Исправление:

CREATE FUNCTION succeed() RETURNS INT AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Несоответствие типа результата. Если вы указываете RETURNS INT, то должны вернуть число. Попробовать вернуть строку в этом случае — плохая идея.

Ошибка в SQL-запросах внутри функции. Всегда проверяйте запросы перед использованием в функцияx. Лучше тестировать их "руками" через psql или pgAdmin.