Функции и PL/pgSQL

Выносим логику внутрь базы: пишем функции на SQL и на процедурном PL/pgSQL.

PL/pgSQL — процедурный язык PostgreSQL, добавляющий к SQL переменные, условия, циклы и обработку ошибок; на нём пишут функции и триггеры.

Простая SQL-функция

Самые лёгкие функции — обёртка над одним запросом. Объявляем имя, параметры, тип результата и тело.

CREATE FUNCTION order_total(p_client TEXT)
RETURNS NUMERIC AS $$
    SELECT COALESCE(SUM(amount), 0)
    FROM orders
    WHERE client = p_client;
$$ LANGUAGE sql;

-- Вызов как обычной функции:
SELECT order_total('Анна');

Тело функции заключают в $$ ... $$ (долларовые кавычки) — это удобный способ не экранировать кавычки внутри. COALESCE подставляет 0, если сумма оказалась NULL (заказов нет).

Функция на PL/pgSQL с логикой

Когда нужны переменные, ветвления и циклы, берут язык plpgsql. Тело оборачивают в блок BEGIN ... END.

CREATE FUNCTION discount(p_amount NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    rate NUMERIC;
BEGIN
    IF p_amount >= 1000 THEN
        rate := 0.10;            -- скидка 10% от 1000
    ELSIF p_amount >= 500 THEN
        rate := 0.05;            -- скидка 5% от 500
    ELSE
        rate := 0;
    END IF;

    RETURN p_amount * (1 - rate);
END;
$$ LANGUAGE plpgsql;

SELECT discount(1200);   -- 1080

Здесь видно отличие от SQL-функции: блок DECLARE объявляет переменные, IF/ELSIF/ELSE ветвит логику, RETURN возвращает результат. Это уже полноценный мини-язык программирования внутри базы.

Зачем выносить логику в базу

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

Обратная сторона: логика в базе сложнее версионировать и тестировать, чем код приложения. Поэтому в базу выносят то, что тесно связано с данными, а бизнес-логику чаще держат в приложении.

Триггеры — функции по событию

Триггер автоматически вызывает функцию при изменении таблицы (INSERT/UPDATE/DELETE). Классическое применение — автоматически проставлять updated_at.

-- Функция, которая обновит метку времени
CREATE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := now();   -- NEW — изменяемая строка
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Повесить её на таблицу перед каждым UPDATE
CREATE TRIGGER trg_updated
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Внутри триггерной функции доступны специальные строки NEW (новая версия) и OLD (старая). Теперь любое изменение строки само обновит updated_at — забыть невозможно.

Итог

Простые функции пишут на LANGUAGE sql, функции с логикой — на plpgsql (переменные, IF, циклы).
Тело оборачивают в долларовые кавычки $$ ... $$; RETURN отдаёт результат.
Триггеры вызывают функцию по событию таблицы; внутри доступны строки NEW и OLD.