Функции и хранимые процедуры в PostgreSQL: PL/pgSQL с нуля

Функции и хранимые процедуры позволяют инкапсулировать логику прямо в базе данных. PostgreSQL поддерживает несколько языков: SQL, PL/pgSQL, Python, JavaScript. Разберём PL/pgSQL — самый распространённый вариант.

Функция vs Процедура

Процедуры появились в PostgreSQL 11. До этого всё делали через функции.

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

-- Функция на чистом SQL (проще всего)
CREATE OR REPLACE FUNCTION get_user_total(p_user_id INTEGER)
RETURNS NUMERIC AS $$
    SELECT COALESCE(SUM(amount), 0)
    FROM orders
    WHERE user_id = p_user_id AND status = 'completed';
$$ LANGUAGE sql STABLE;

-- Вызов
SELECT get_user_total(42);
SELECT u.name, get_user_total(u.id) AS total FROM users u;

Атрибуты стабильности:

• VOLATILE (по умолчанию) — может менять БД, вызывается каждый раз
• STABLE — не меняет БД, одинаковый результат в транзакции
• IMMUTABLE — одинаковый результат всегда (можно кешировать, использовать в индексах)

PL/pgSQL функция

CREATE OR REPLACE FUNCTION calculate_discount(
    p_amount   NUMERIC,
    p_tier     TEXT
) RETURNS NUMERIC AS $$
DECLARE
    v_discount_pct NUMERIC;
    v_result       NUMERIC;
BEGIN
    -- Условие
    v_discount_pct := CASE p_tier
        WHEN 'gold'   THEN 0.15
        WHEN 'silver' THEN 0.10
        WHEN 'bronze' THEN 0.05
        ELSE 0
    END;

    v_result := p_amount * (1 - v_discount_pct);

    RETURN v_result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Использование
SELECT calculate_discount(1000, 'gold');  -- 850

Переменные и блоки

CREATE OR REPLACE FUNCTION get_user_stats(p_user_id INTEGER)
RETURNS TABLE(
    total_orders    INTEGER,
    total_spent     NUMERIC,
    avg_order       NUMERIC,
    last_order_date DATE
) AS $$
DECLARE
    v_user_exists BOOLEAN;
BEGIN
    -- Проверить существование
    SELECT EXISTS(SELECT 1 FROM users WHERE id = p_user_id)
    INTO v_user_exists;

    IF NOT v_user_exists THEN
        RAISE EXCEPTION 'User % not found', p_user_id;
    END IF;

    RETURN QUERY
    SELECT
        COUNT(*)::INTEGER,
        COALESCE(SUM(o.amount), 0),
        COALESCE(AVG(o.amount), 0),
        MAX(o.created_at)::DATE
    FROM orders o
    WHERE o.user_id = p_user_id AND o.status = 'completed';
END;
$$ LANGUAGE plpgsql STABLE;

-- Вызов
SELECT * FROM get_user_stats(42);

IF / ELSIF / ELSE

CREATE OR REPLACE FUNCTION get_user_segment(p_total_spent NUMERIC)
RETURNS TEXT AS $$
BEGIN
    IF p_total_spent >= 100000 THEN
        RETURN 'whale';
    ELSIF p_total_spent >= 10000 THEN
        RETURN 'loyal';
    ELSIF p_total_spent >= 1000 THEN
        RETURN 'regular';
    ELSIF p_total_spent > 0 THEN
        RETURN 'new';
    ELSE
        RETURN 'inactive';
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Цикл FOR

CREATE OR REPLACE FUNCTION bulk_update_streaks()
RETURNS INTEGER AS $$
DECLARE
    v_user   RECORD;
    v_count  INTEGER := 0;
BEGIN
    FOR v_user IN
        SELECT id, last_activity_date
        FROM users
        WHERE is_active = true
    LOOP
        IF v_user.last_activity_date = CURRENT_DATE - 1 THEN
            UPDATE users SET streak_days = streak_days + 1 WHERE id = v_user.id;
            v_count := v_count + 1;
        ELSIF v_user.last_activity_date < CURRENT_DATE - 1 THEN
            UPDATE users SET streak_days = 0 WHERE id = v_user.id;
        END IF;
    END LOOP;

    RETURN v_count;  -- Сколько пользователей обновили стрик
END;
$$ LANGUAGE plpgsql;

⚠️ Цикл по строкам медленнее SET-based SQL. Если можно переписать запросом — лучше запрос. Цикл оправдан для сложной логики с ветвлениями.

Обработка ошибок: EXCEPTION

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE 'Unexpected error: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT safe_divide(10, 0);  -- NULL вместо ошибки

RAISE — логирование

-- Уровни сообщений
RAISE DEBUG   'Debug: %', value;
RAISE NOTICE  'Notice: %', value;   -- выводится клиенту
RAISE WARNING 'Warning: %', value;
RAISE EXCEPTION 'Error: %', value;  -- прерывает выполнение

-- С кодом ошибки
RAISE EXCEPTION 'User % not found', p_user_id
    USING ERRCODE = 'no_data_found';

Процедуры (PostgreSQL 11+)

CREATE OR REPLACE PROCEDURE transfer_credits(
    p_from_user_id INTEGER,
    p_to_user_id   INTEGER,
    p_amount       NUMERIC
)
LANGUAGE plpgsql AS $$
BEGIN
    -- Проверка баланса
    IF (SELECT credits FROM users WHERE id = p_from_user_id) < p_amount THEN
        RAISE EXCEPTION 'Insufficient credits';
    END IF;

    UPDATE users SET credits = credits - p_amount WHERE id = p_from_user_id;
    UPDATE users SET credits = credits + p_amount WHERE id = p_to_user_id;

    -- Запись транзакции
    INSERT INTO credit_transactions (from_user, to_user, amount, created_at)
    VALUES (p_from_user_id, p_to_user_id, p_amount, NOW());

    COMMIT;  -- Только в процедурах!
END;
$$;

-- Вызов
CALL transfer_credits(1, 2, 100);

Когда использовать функции

Стоит использовать:

• Сложная бизнес-логика, которая нужна нескольким приложениям
• Расчёты, используемые в индексах (функциональный индекс)
• Агрегатные и оконные функции для повторного использования
• Триггеры (тело триггера — функция)

Не стоит:

• Простые запросы — лучше в приложении
• Критичный для производительности код — ORM и кеш эффективнее
• Бизнес-логика, которая часто меняется — миграции функций сложнее кода

Итог

-- Создать или заменить
CREATE OR REPLACE FUNCTION/PROCEDURE name(params) RETURNS type AS $$
DECLARE
    -- переменные
BEGIN
    -- логика
    RETURN value;  -- только для функций
EXCEPTION
    WHEN condition THEN ...;
END;
$$ LANGUAGE plpgsql [STABLE|IMMUTABLE];

-- Удалить
DROP FUNCTION name(param_types);

-- Список функций
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'public';

PL/pgSQL — полноценный процедурный язык. Его стоит знать каждому, кто серьёзно работает с PostgreSQL.