Stored procedures: packaging logic inside the database

Understanding Stored procedures: packaging logic inside the database

A stored procedure is a program the server keeps in its own catalog under a name, held as source text and turned into a plan when a session first runs it, and invoked as a statement in its own right: CALL transfer(1, 2, 200.00) in PostgreSQL and MySQL, EXEC transfer 1, 2, 200.00 in SQL Server. The point is not saving typing, it is moving a sequence of statements to the side of the connection where the data lives, so a debit and its matching credit happen with no network hop between them and no chance that a dropped client leaves half the work done. Because a procedure is a statement and not an expression, it has no return type and cannot appear in a SELECT list or a subquery; it exists to change something, not to produce a value.

The dividing line worth memorising is transaction ownership. A function is evaluated inside a statement that is already running inside a transaction, so it has no coherent point at which it could commit; a procedure invoked by CALL is itself the top-level statement, so it may issue COMMIT and ROLLBACK, which is what makes procedures the right shape for backfills and queue draining that would otherwise hold one enormous transaction open. The price is that atomicity becomes your decision: a body with no COMMIT in it is all-or-nothing, while one that commits every thousand rows leaves the committed prefix in place if it fails at row five thousand.

Choosing what belongs in a procedure is mostly a question of who must be protected from what. Rules that have to hold no matter which client connects, the reporting script, the mobile API, a person with a psql prompt open, belong on the server, as does bulk maintenance that would be pointless to stream over the wire row by row. Everything else pays real costs: bodies are written in a language that does not port (PL/pgSQL, T-SQL, MySQL's SQL/PSM), they are awkward to unit test and to read in a diff, and they ship by DDL rather than with your build. A procedure that mainly formats output or stitches together two unrelated features is usually application code that ended up in the wrong process.

-- PostgreSQL
CREATE TABLE accounts (
    id      int PRIMARY KEY,
    holder  text NOT NULL,
    balance numeric(10,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'ana', 500.00), (2, 'bo', 50.00);

CREATE PROCEDURE transfer(p_from int, p_to int, p_amount numeric)
LANGUAGE plpgsql
AS $$
DECLARE
    src numeric;
BEGIN
    SELECT balance INTO src FROM accounts WHERE id = p_from FOR UPDATE;

    IF src IS NULL THEN
        RAISE EXCEPTION 'account % does not exist', p_from;
    END IF;

    IF src < p_amount THEN
        RAISE EXCEPTION 'account % has % but needs %', p_from, src, p_amount;
    END IF;

    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
END;
$$;

CALL transfer(1, 2, 200.00);

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

Worked examples

-- PostgreSQL
CREATE TABLE jobs (id int PRIMARY KEY, done boolean NOT NULL DEFAULT false);
INSERT INTO jobs (id) SELECT g FROM generate_series(1, 5) AS g;

CREATE PROCEDURE drain_jobs(p_batch int)
LANGUAGE plpgsql
AS $$
DECLARE
    n int;
BEGIN
    LOOP
        UPDATE jobs SET done = true
        WHERE id IN (SELECT id FROM jobs WHERE NOT done ORDER BY id LIMIT p_batch);

        GET DIAGNOSTICS n = ROW_COUNT;
        EXIT WHEN n = 0;

        RAISE NOTICE 'committed % row(s)', n;
        COMMIT;
    END LOOP;
END;
$$;

CALL drain_jobs(2);

-- PostgreSQL
CREATE TABLE tickets (
    id    int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL
);

CREATE PROCEDURE open_ticket(p_title text, OUT new_id int)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO tickets (title) VALUES (p_title)
    RETURNING id INTO new_id;
END;
$$;

CALL open_ticket('printer on fire', NULL);

Important notes

Common mistakes

Try it yourself