Створення простих функцій: 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 = 'складено';

    -- Перевіряємо умову
    IF passed_exams < 5 THEN
        RETURN 'Студент не склав достатньо іспитів';
    END IF;

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

    RETURN 'Студента переведено!';
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-запитах всередині функції. Завжди перевіряй запити перед використанням у функціях. Краще тестувати їх "руками" через psql або pgAdmin.