Відмінності функцій і процедур

У багатьох мовах програмування між функціями і процедурами майже немає різниці. В SQL вона є. У PostgreSQL функції та процедури — це не просто два різних способи запускати код. Це різні парадигми мислення.

Функція в SQL не може змінювати дані в базі. Вона повинна працювати тільки з переданими даними і повертати результат на їх основі. Її створюють для того, щоб використовувати всередині SELECT-запитів.

Процедура в SQL створена для зміни бази. Тому вона може працювати з транзакціями (на відміну від функцій), писати щось у базу. І не може використовуватись всередині SELECT-запитів.

Ось коротке їх порівняння:

Відмінності в SQL

У звичайному SQL функція схожа на вираз: вона обчислює і повертає значення. Процедура — це інструкція: вона робить щось, але не бере участі у виразах.

Функція в SQL

SELECT calculate_discount(200);

• Може бути у WHERE, ORDER BY, INSERT, UPDATE і т.д.
• Повинна бути чистою: не повинна змінювати стан бази (якщо IMMUTABLE/STABLE).

Процедура в SQL

CALL process_order(123);

• Не повертає результат.
• Може робити COMMIT, ROLLBACK, викликати RAISE, запускати цикли.

Відмінності в PL/pgSQL

Функції в PostgreSQL можна уявити як групу обчислень. Вони дуже гнучкі: можна передавати параметри, використовувати умовні оператори, цикли, курсори, підзапити, повертати рядки, скаляри, таблиці.

Функції в PL/pgSQL

CREATE FUNCTION square(x INT) RETURNS INT AS $$
BEGIN
    RETURN x * x;
END;
$$ LANGUAGE plpgsql;

Особливості:

• Обов'язковий RETURNS
• Може використовувати DECLARE, BEGIN, END, LOOP, IF, CASE
• Не можна виконувати COMMIT/ROLLBACK
• Можна викликати у SELECT, UPDATE, CHECK, WHERE, RETURNING

Виклик:

SELECT square(5);  -- поверне 25

Процедури в PL/pgSQL

Процедури — це механізм керування діями. Вони ідеально підходять, коли треба:

• виконувати багато кроків з логікою;
• оновлювати і вставляти великі об'єми даних;
• юзати керування транзакціями: COMMIT, ROLLBACK, SAVEPOINT.

CREATE PROCEDURE log_event(msg TEXT) AS $$
BEGIN
    INSERT INTO logs(message) VALUES (msg);
    COMMIT;
END;
$$ LANGUAGE plpgsql;

Особливості:

• Немає RETURNS
• Викликається тільки через CALL
• Дозволено використовувати COMMIT, ROLLBACK, SAVEPOINT
• Підходить для пакетної обробки, міграцій, ETL

Виклик:

CALL log_event('Обробка завершена');

Чому функції і процедури розділені

Тому що у них різні цілі в SQL:

Ключова перевага процедури — COMMIT

Процедури можуть керувати транзакціями всередині себе. Тобто прямо всередині процедури можна робити:

BEGIN;
-- логіка
SAVEPOINT point1;
-- спроба оновлення
ROLLBACK TO point1;
COMMIT;

А у функції COMMIT і ROLLBACK заборонені. Якщо ти спробуєш — отримаєш: ERROR: invalid transaction termination in function

Це означає, що функція має бути детермінованою і безпечною, а процедура може бути "брудною роботою" — чистити, логувати, вставляти.

Порівняльна таблиця

Де що використовувати?

Використовуй функцію, якщо:

• хочеш повернуте значення;
• викликаєш у SELECT, фільтруєш дані;
• це простий розрахунок, перевірка або обгортка для SQL.

Використовуй процедуру, якщо:

• хочеш виконувати складні дії;
• потрібен контроль транзакцій;
• обробляєш батчі, переносиш дані, архівуєш, логіруєш.