Основні можливості PL/pgSQL

Отже, давай заглибимось у те, що робить PL/pgSQL таким потужним і незамінним інструментом для розробників і адміністраторів баз даних. На цій лекції ми поговоримо про переваги PL/pgSQL, його унікальні можливості й розглянемо приклади, які покажуть, як ці можливості можуть бути корисні в реальному житті.

Щоб зрозуміти, навіщо нам PL/pgSQL, уяви, що ти знаходишся у світі, де будь-яке завдання програмування треба виконувати виключно за допомогою SQL. Наприклад, щоб порахувати кількість студентів по кожному факультету, тобі довелося б написати складний SQL-запит, а потім обробляти його результати на стороні клієнта. Це не дуже ефективно, правда? Ось тут і з'являється PL/pgSQL з його підтримкою змінних, циклів, умов і обробкою помилок.

Плюси використання PL/pgSQL:

1. Логіка на стороні сервера: PL/pgSQL дозволяє скоротити кількість даних, які передаються між сервером і клієнтом, бо вся логіка виконується на сервері. Це зменшує мережеві затримки.
2. Продуктивність: функції на PL/pgSQL компілюються і зберігаються в базі даних, що робить їх виконання швидшим, ніж виконання серії окремих SQL-запитів.
3. Автоматизація задач: за допомогою PL/pgSQL можна автоматизувати рутинні операції, такі як оновлення даних, логування чи перевірка цілісності інформації.
4. Бізнес-логіка: PL/pgSQL дозволяє реалізувати складну бізнес-логіку, таку як розрахунки, перевірки чи створення аналітичних звітів.
5. Зручність і читабельність: код на PL/pgSQL легко структурувати, розбивати на функції й покращувати, що робить його зручним для супроводу.

Сфери застосування PL/pgSQL

Тепер давай розглянемо, де саме можна застосувати PL/pgSQL і як він вирішує реальні задачі.

1. Автоматизація рутинних операцій

PL/pgSQL дозволяє автоматизувати повторювані задачі. Наприклад, тобі потрібно щодня оновлювати певні дані або періодично запускати аналіз. Створивши функцію на PL/pgSQL, ти можеш легко інтегрувати її з планувальником задач (наприклад, pg_cron) для виконання у визначений час.

Приклад: автоматичне оновлення статусу

CREATE FUNCTION update_student_status() RETURNS VOID AS $$
BEGIN
    UPDATE students
    SET status = 'neaktyvnyi'
    WHERE last_login < NOW() - INTERVAL '1 year';
    RAISE NOTICE 'Statusy studentiv onovleni.';
END;
$$ LANGUAGE plpgsql;

Ця функція, наприклад, автоматично задає статус "неактивний" для студентів, які не заходили в систему більше року.

2. Генерація звітів

PL/pgSQL чудово підходить для створення аналітичних звітів, де треба агрегувати й об'єднувати дані з кількох таблиць. Можна створити процедури для автоматичної генерації звітів і їх запису в окремі таблиці.

Приклад: створення звіту по кількості студентів по факультетах

CREATE FUNCTION generate_faculty_report() RETURNS TABLE (faculty_id INT, student_count INT) AS $$
BEGIN
    RETURN QUERY
    SELECT faculty_id, COUNT(*)
    FROM students
    GROUP BY faculty_id;
END;
$$ LANGUAGE plpgsql;

Після виклику цієї функції ти отримаєш статистику по всіх факультетах.

3. Логування змін у таблицях

Логування — це процес запису змін даних у таблицях бази даних. PL/pgSQL дозволяє ефективно реалізувати це завдання, наприклад, за допомогою тригерів.

Приклад функції для логування змін

CREATE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO change_logs(table_name, operation, old_data, new_data, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, ROW_TO_JSON(OLD), ROW_TO_JSON(NEW), NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Ця функція записує в таблицю change_logs дані про те, яка таблиця була змінена, яка операція виконана (наприклад, INSERT, UPDATE, DELETE), а також фіксує старі й нові дані.

4. Реалізація складних алгоритмів

З PL/pgSQL ти можеш програмувати алгоритми, які виходять за межі можливостей стандартного SQL. Наприклад, це можуть бути розрахунки витрат, перевірка бізнес-правил чи автоматична генерація ідентифікаторів.

Приклад: генерація унікального ідентифікатора

CREATE FUNCTION generate_unique_id() RETURNS TEXT AS $$
BEGIN
    RETURN CONCAT('UID-', EXTRACT(EPOCH FROM NOW()), '-', RANDOM()::TEXT);
END;
$$ LANGUAGE plpgsql;

Ця функція створює унікальний ідентифікатор, додаючи поточну часову мітку й випадкове число.

5. Робота з тригерами

Тригери і PL/pgSQL йдуть рука об руку. Коли тобі треба автоматизувати якусь задачу, наприклад, оновлення пов'язаних даних, тригери з функціями PL/pgSQL — ідеальний інструмент.

Приклад: тригер на видалення студентів

CREATE FUNCTION handle_delete_students() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM enrollments WHERE student_id = OLD.id;
    RAISE NOTICE 'Zapis pro zareyestrovani studentiv % vydaleno.', OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Використовуючи цю функцію, ти можеш, наприклад, автоматично видаляти записи про зарахування студентів з таблиці enrollments, якщо студента видаляють з таблиці students.

6. Обробка помилок

Коли мова йде про складні задачі, обробка помилок стає критично важливою. PL/pgSQL надає блок EXCEPTION, який дозволяє перехоплювати й обробляти помилки.

Приклад: обробка помилок

CREATE FUNCTION insert_student(name TEXT, faculty_id INT) RETURNS VOID AS $$
BEGIN
    INSERT INTO students(name, faculty_id) VALUES (name, faculty_id);
EXCEPTION
    WHEN FOREIGN_KEY_VIOLATION THEN
        RAISE NOTICE 'Faculty ID % ne isnue!', faculty_id;
END;
$$ LANGUAGE plpgsql;

Тут, якщо виникне помилка введення значення факультету, якого немає в базі, буде виведено попередження замість аварійного завершення.

Приклади складних задач, які вирішуються з PL/pgSQL

Щоб надихнути тебе на використання PL/pgSQL, ось кілька прикладів задач, які він допомагає вирішувати:

1. Автоматичне оновлення знижок в інтернет-магазині Функція, яка щодня оновлює знижки для товарів, у яких закінчується термін акції.

2. Перевірка й виправлення даних Функція, яка перевіряє таблицю на наявність дубльованих записів і видаляє їх.

3. Швидке перемикання конфігурацій Функція, яка дозволяє змінити параметри системи, наприклад, перемкнути режим роботи застосунку.

Реальні приклади зі світу IT

PL/pgSQL використовують мільйони компаній по всьому світу. Наприклад:

• Інтернет-магазини застосовують функції для розрахунку податків, автоматичного оновлення знижок і генерації звітів про продажі.
• Банки використовують PL/pgSQL для обробки тисяч операцій на день — від нарахування відсотків до перевірки кредитного рейтингу.
• Соціальні мережі впроваджують складні алгоритми обробки даних, наприклад, для підбору рекомендованих друзів.

PL/pgSQL — це свого роду швейцарський ніж для програмістів, які працюють з PostgreSQL. Він не лише спрощує роботу з базою даних, а й дозволяє реалізувати задачі, які в звичайному SQL були б або складними, або взагалі неможливими. І що найголовніше — PL/pgSQL легко вчиться, і з ним кожен зможе відчути себе справжнім майстром баз даних.