Отличия функций и процедур

Во многих языках программирования между функциями и процедурами почти нет разницы. В 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.

Используйте процедуру, если:

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