Збережені процедури

CALL procedure_name(argument1, argument2);

-- Так не можна:
SELECT procedure_name(10);

COMMIT;

ROLLBACK;

CALL process_operation();

BEGIN;

CALL process_operation();

COMMIT;

DROP TABLE IF EXISTS account_entries;
DROP TABLE IF EXISTS transfers;
DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner_name text NOT NULL,
    balance numeric(12, 2) NOT NULL DEFAULT 0,
    CHECK (balance >= 0)
);

CREATE TABLE transfers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    from_account_id integer NOT NULL REFERENCES accounts(id),
    to_account_id integer NOT NULL REFERENCES accounts(id),
    amount numeric(12, 2) NOT NULL CHECK (amount > 0),
    reference text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE TABLE account_entries (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    transfer_id bigint NOT NULL REFERENCES transfers(id),
    account_id integer NOT NULL REFERENCES accounts(id),
    amount numeric(12, 2) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

INSERT INTO accounts (owner_name, balance)
VALUES
    ('Олена', 1000.00),
    ('Андрій', 250.00);

CREATE OR REPLACE PROCEDURE transfer_money(
    IN p_from_account_id integer,
    IN p_to_account_id integer,
    IN p_amount numeric(12, 2),
    IN p_reference text
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_first_id integer;
    v_first_balance numeric(12, 2);
    v_second_id integer;
    v_second_balance numeric(12, 2);

    v_from_balance numeric(12, 2);
    v_to_balance numeric(12, 2);
    v_transfer_id bigint;
BEGIN
    IF p_from_account_id IS NULL
       OR p_to_account_id IS NULL
       OR p_amount IS NULL
       OR p_reference IS NULL THEN
        RAISE EXCEPTION
            USING
                ERRCODE = '22023',
                MESSAGE = 'Усі параметри переказу є обов’язковими';
    END IF;

    IF p_from_account_id = p_to_account_id THEN
        RAISE EXCEPTION
            USING
                ERRCODE = '22023',
                MESSAGE = 'Рахунок-відправник і рахунок-отримувач мають відрізнятися';
    END IF;

    IF p_amount <= 0 THEN
        RAISE EXCEPTION
            USING
                ERRCODE = '22023',
                MESSAGE = 'Сума переказу повинна бути більшою за нуль';
    END IF;

    IF btrim(p_reference) = '' THEN
        RAISE EXCEPTION
            USING
                ERRCODE = '22023',
                MESSAGE = 'Призначення переказу не може бути порожнім';
    END IF;

    /*
     * Рахунки блокуються завжди в однаковому порядку.
     * Це зменшує ризик взаємного блокування під час паралельних переказів.
     */
    SELECT id, balance
    INTO STRICT v_first_id, v_first_balance
    FROM accounts
    WHERE id = LEAST(p_from_account_id, p_to_account_id)
    FOR UPDATE;

    SELECT id, balance
    INTO STRICT v_second_id, v_second_balance
    FROM accounts
    WHERE id = GREATEST(p_from_account_id, p_to_account_id)
    FOR UPDATE;

    IF p_from_account_id = v_first_id THEN
        v_from_balance := v_first_balance;
        v_to_balance := v_second_balance;
    ELSE
        v_from_balance := v_second_balance;
        v_to_balance := v_first_balance;
    END IF;

    IF v_from_balance < p_amount THEN
        RAISE EXCEPTION
            USING
                ERRCODE = 'P0001',
                MESSAGE = format(
                    'Недостатньо коштів: доступно %s, потрібно %s',
                    v_from_balance,
                    p_amount
                );
    END IF;

    UPDATE accounts
    SET balance = balance - p_amount
    WHERE id = p_from_account_id;

    UPDATE accounts
    SET balance = balance + p_amount
    WHERE id = p_to_account_id;

    INSERT INTO transfers (
        from_account_id,
        to_account_id,
        amount,
        reference
    )
    VALUES (
        p_from_account_id,
        p_to_account_id,
        p_amount,
        btrim(p_reference)
    )
    RETURNING id INTO v_transfer_id;

    INSERT INTO account_entries (
        transfer_id,
        account_id,
        amount
    )
    VALUES
        (v_transfer_id, p_from_account_id, -p_amount),
        (v_transfer_id, p_to_account_id, p_amount);

    COMMIT;
END;
$$;

CALL transfer_money(
    1,
    2,
    125.50,
    'Оплата рахунку INV-2026-001'
);

SELECT id, owner_name, balance
FROM accounts
ORDER BY id;

 id | owner_name | balance
----+------------+---------
  1 | Олена      |  874.50
  2 | Андрій     |  375.50

SELECT
    t.id,
    t.from_account_id,
    t.to_account_id,
    t.amount,
    t.reference,
    t.created_at
FROM transfers AS t
ORDER BY t.id;

SELECT
    ae.transfer_id,
    ae.account_id,
    ae.amount
FROM account_entries AS ae
ORDER BY ae.id;

SELECT
    transfer_id,
    sum(amount) AS total
FROM account_entries
GROUP BY transfer_id;

CALL transfer_money(
    1,
    2,
    100000.00,
    'Невдалий переказ'
);

SELECT id, owner_name, balance
FROM accounts
ORDER BY id;

SELECT count(*) AS transfer_count
FROM transfers;

SELECT ...
FROM accounts
WHERE id = ...
FOR UPDATE;

WHERE id = LEAST(p_from_account_id, p_to_account_id)

WHERE id = GREATEST(p_from_account_id, p_to_account_id)

CREATE OR REPLACE PROCEDURE calculate_total(
    IN p_price numeric,
    IN p_quantity integer,
    OUT p_total numeric
)
LANGUAGE plpgsql
AS $$
BEGIN
    p_total := p_price * p_quantity;
END;
$$;

CALL calculate_total(19.99, 3, NULL);

CREATE OR REPLACE PROCEDURE transfer_money(
    IN p_from_account_id integer,
    IN p_to_account_id integer,
    IN p_amount numeric(12, 2),
    IN p_reference text
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- нова реалізація
END;
$$;

DROP PROCEDURE transfer_money(integer, integer, numeric, text);

DROP PROCEDURE transfer_money(integer, integer, numeric, text);

DROP PROCEDURE IF EXISTS transfer_money(integer, integer, numeric, text);

SELECT transfer_money(1, 2, 100, 'Тест');

CALL transfer_money(1, 2, 100, 'Тест');

BEGIN;

CALL transfer_money(1, 2, 100, 'Тест');

COMMIT;

SELECT balance
FROM accounts
WHERE id = p_from_account_id;

SELECT balance
FROM accounts
WHERE id = p_from_account_id
FOR UPDATE;

BEGIN
    -- операції
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;