Як писати функції на PL/pgSQL і коли логіку варто тримати в базі?

PL/pgSQL - процедурна мова PostgreSQL: змінні, умови, цикли, обробка винятків поверх SQL.

CREATE FUNCTION transfer(from_id bigint, to_id bigint, amount numeric)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount
    WHERE id = from_id AND balance >= amount;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient funds on account %', from_id
            USING ERRCODE = 'check_violation';
    END IF;

    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$;

SELECT transfer(1, 2, 500);

Функція чи процедура:

Прості функції - на SQL, а не PL/pgSQL: LANGUAGE sql функцію планувальник може вбудувати в запит, а тіло PL/pgSQL для нього - «чорна скринька».

Коли логіка в базі виправдана:

• інваріанти даних, які мають виконуватися незалежно від того, хто пише в базу (застосунок, скрипт, адмін через psql): тригери, обмеження;
• обробка великих обсягів даних без передачі їх у застосунок і назад;
• атомарні операції, які інакше потребували б кількох запитів з блокуваннями.

Коли - ні (і це більшість бізнес-логіки):

• тестування й налагодження складніші: немає звичних інструментів, покриття, IDE;
• версіонування й деплой: код функції живе в міграціях, рев'ю й відкат незручні;
• масштабування: база - найважче для масштабування місце, а обчислення в ній додають навантаження;
• розподіл знань: команда пише на PHP, а критичний код - на іншій мові.

У Laravel: функції створюють у міграціях (DB::unprepared() для тіла з $$), викликають через DB::select('SELECT transfer(?, ?, ?)', [...]). Винятки з RAISE EXCEPTION приходять як QueryException з кодом SQLSTATE, який можна перевірити.

Безпека: динамічний SQL усередині функції (EXECUTE) - лише з format('%I', ...) для ідентифікаторів і USING для значень, інакше SQL-ін'єкція переїжджає з PHP у базу.

Докладніше в документації: PL/pgSQL