Calling Procedures and Functions Inside Transactions

In modern database systems, business logic is often implemented server-side — using procedures and functions. When working with PostgreSQL, it's important to get the difference between functions and procedures (especially since procedures showed up in version 11+) and how they interact with transactions.

Below, I'll walk you through the main facts about transaction mechanics, nested calls, and partial rollbacks in PostgreSQL 17 procedures/functions, according to the official docs and current limitations.

Key Concepts: Functions vs Procedures

Function (CREATE FUNCTION) — always runs inside a single outer transaction; you can't use explicit transaction commands (BEGIN, COMMIT, ROLLBACK, SAVEPOINT) inside functions.

• Any changes are committed or rolled back only at the outer transaction level.
• For "partial rollback" inside functions, you use BEGIN ... EXCEPTION ... END, but you can't do commits inside a function.

Procedure (CREATE PROCEDURE) — was added to let you manage transactions right on the server (like doing partial commits, rolling back steps, etc.).

• In procedures (PL/pgSQL), you can use COMMIT and ROLLBACK to manage transactions.
• IMPORTANT: SAVEPOINT, RELEASE SAVEPOINT, and ROLLBACK TO SAVEPOINT are all forbidden in PL/pgSQL (any of them will throw a syntax error). For "partial rollback" inside functions and procedures, use a nested BEGIN ... EXCEPTION ... END block — it creates an implicit savepoint and, on error, rolls back only the changes inside the block.
• You can only call procedures with a separate SQL command CALL ..., not via SELECT or inside other functions.

How do you call one procedure/function from another?

Functions "transparently" call other functions by just using their name:

-- Example: function to calculate discount
CREATE OR REPLACE FUNCTION calculate_discount(order_total NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF order_total >= 100 THEN
        RETURN order_total * 0.1;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Order processing function calls another function
CREATE OR REPLACE FUNCTION process_order(order_id INT, order_total NUMERIC)
RETURNS VOID AS $$
DECLARE
    discount NUMERIC;
BEGIN
    discount := calculate_discount(order_total);
    RAISE NOTICE 'Discount: %', discount;
    INSERT INTO orders_log (order_id, order_total, discount)
    VALUES (order_id, order_total, discount);
END;
$$ LANGUAGE plpgsql;

Everything runs inside a single outer transaction! An error in any function will roll back all changes.

Calling Procedures and Nested Transactions

Procedures can be called inside other procedures using the CALL ... command (PostgreSQL 17 allows a call stack like CALL proc1() -> CALL proc2()), but the transaction rules still apply:

• Transaction commands (COMMIT, ROLLBACK) are only available at the top level of procedures. SAVEPOINT, RELEASE SAVEPOINT, and ROLLBACK TO SAVEPOINT are not allowed inside PL/pgSQL at all.
• If a procedure with transaction management is called inside an already active explicit transaction (like from a client without autocommit), trying to do COMMIT will throw an error.

Example: Procedure with Transaction Management

-- Procedure with step-by-step commit (works only in autocommit connection mode)
CREATE PROCEDURE process_batch_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT order_id, order_total FROM incoming_orders LOOP
        BEGIN
            -- Save each batch of data separately
            INSERT INTO orders (order_id, total) VALUES (rec.order_id, rec.order_total);
        EXCEPTION WHEN OTHERS THEN
            INSERT INTO order_errors(order_id, err_text) VALUES (rec.order_id, SQLERRM);
        END;
        COMMIT;
    END LOOP;
END;
$$;

-- Call the procedure
CALL process_batch_orders();

After each COMMIT, a new transaction automatically starts.

Partial Rollback (savepoint-like behavior) in PL/pgSQL

PL/pgSQL (both in functions and procedures) does not support the ROLLBACK TO SAVEPOINT command.

To roll back changes in part of your code, you only use the BEGIN ... EXCEPTION ... END block:

BEGIN
    -- some actions
    BEGIN
        -- potentially error-prone operation
    EXCEPTION WHEN OTHERS THEN
        -- all changes in this block will be rolled back
        RAISE NOTICE 'Rollback inside block!';
    END;
END;

In procedures you can't use SAVEPOINT, RELEASE SAVEPOINT, or ROLLBACK TO SAVEPOINT either — these commands are forbidden in PL/pgSQL. To separate stages with a possibility of partial rollback, you can only manage them through exception handling.

Limitations and Best Practices

1. Functions — only atomic operations: all or nothing. If something goes wrong — all changes are rolled back.
2. Procedures — only via CALL: and only as a separate SQL command, not from SELECT/functions. Nested transaction management is possible, but only if you follow PL/pgSQL's rules strictly.
3. Partial rollback — only via EXCEPTION: the officially recommended and supported way for partial rollback (like SAVEPOINT).
4. Nested procedures can manage transactions only when called via CALL: otherwise you'll get an error.

Questions About Logic and Transaction Interaction

Can I do a "nested" transaction inside a function?

Nope. Everything runs in one transaction. For partial rollback — only EXCEPTION blocks.

Can I do COMMIT/ROLLBACK inside a function or anonymous block?

Nope, that's a syntax error. Use procedures.

Can I call a procedure from a function?

Nope, only with the CALL command. Not from a function/SELECT.

Can I do ROLLBACK TO SAVEPOINT in a procedure?

Nope! In PL/pgSQL that's not allowed. Use EXCEPTION blocks.

2

Task

SQL SELF, level 53, lesson 1

Locked

Creating and Calling Procedures for Calculating and Logging Discounts

Creating and Calling Procedures for Calculating and Logging Discounts

Open