PL/pgSQL Stored Procedures

Master PL/pgSQL stored procedures — CREATE PROCEDURE syntax, CALL invocation, the procedure-vs-function distinction, and procedures' unique transaction control via COMMIT/ROLLBACK. Critical fix: original tutorial had a stray semicolon that prematurely ended the CREATE PROCEDURE statement.

A stored procedure is a named block of PL/pgSQL code that performs a task — typically a multi-step operation that modifies data. Procedures landed in PostgreSQL with version 11 (2018), and they fill a specific gap in what functions could already do.

The transaction-control superpower is the main reason procedures exist. A function can't COMMIT mid-execution; a procedure can. That makes procedures the right tool for batch jobs, data migrations, and any operation that needs to commit work in chunks.

Parameters in procedures can have IN (default) or INOUT modes — but not OUT. To return values from a procedure, use INOUT parameters (covered on the next page).

A procedure can use RETURN with no expression to exit early:

But it cannot RETURN expression; — there's nowhere for the value to go.

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
    id      INT GENERATED BY DEFAULT AS IDENTITY,
    name    VARCHAR(100) NOT NULL,
    balance NUMERIC(15, 2) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO accounts (name, balance) VALUES
    ('Bob',   10000),
    ('Alice', 10000);

SELECT * FROM accounts;

Move money from one account to another. Two updates wrapped in a single procedure body — both happen, or neither does:

DROP PROCEDURE IF EXISTS transfer(INT, INT, NUMERIC);

CREATE OR REPLACE PROCEDURE transfer(
    sender   INT,
    receiver INT,
    amount   NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- subtract from the sender's account
    UPDATE accounts
    SET    balance = balance - amount
    WHERE  id = sender;

    -- add to the receiver's account
    UPDATE accounts
    SET    balance = balance + amount
    WHERE  id = receiver;
END;
$$;

Now call it:

CALL transfer(1, 2, 1000);
SELECT * FROM accounts;

$1,000 moved from Bob to Alice. The two UPDATEs ran inside a single transaction (the implicit one wrapping the CALL) — both succeeded together.

Add a sanity check: refuse the transfer if the sender doesn't have enough. Use RAISE EXCEPTION to abort, which rolls back any partial work:

CREATE OR REPLACE PROCEDURE safe_transfer(
    sender   INT,
    receiver INT,
    amount   NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    sender_balance NUMERIC;
BEGIN
    SELECT balance INTO sender_balance
    FROM   accounts
    WHERE  id = sender
    FOR    UPDATE;          -- lock the row to prevent races

    IF sender_balance IS NULL THEN
        RAISE EXCEPTION 'Sender account % does not exist', sender;
    END IF;

    IF sender_balance < amount THEN
        RAISE EXCEPTION 'Insufficient funds: % has %, requested %',
                        sender, sender_balance, amount;
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = sender;
    UPDATE accounts SET balance = balance + amount WHERE id = receiver;

    RAISE NOTICE 'Transferred % from % to %', amount, sender, receiver;
END;
$$;

CALL safe_transfer(1, 2, 50000);  -- Bob only has 9000

The transaction aborts; no rows changed. The error message names the offending account and the actual balance — much friendlier than a silent corruption.

The killer feature: a procedure can COMMIT its work mid-execution and start a new transaction. Useful for batch jobs that should commit progress periodically rather than holding everything in one massive transaction:

CREATE OR REPLACE PROCEDURE log_balances_in_batches()
LANGUAGE plpgsql
AS $$
DECLARE
    rec        record;
    batch_size INTEGER := 100;
    counter    INTEGER := 0;
BEGIN
    FOR rec IN SELECT id, balance FROM accounts ORDER BY id LOOP
        -- imagine: insert into an audit log here
        RAISE NOTICE 'Account %: %', rec.id, rec.balance;
        counter := counter + 1;

        -- Every batch_size rows, commit and start fresh
        IF counter % batch_size = 0 THEN
            COMMIT;
            RAISE NOTICE 'Committed batch ending at id %', rec.id;
        END IF;
    END LOOP;

    -- Commit the final partial batch
    COMMIT;
END;
$$;

CALL log_balances_in_batches();

1. Use DROP PROCEDURE IF EXISTS at the top of deployment scripts. CREATE OR REPLACE alone won't drop a procedure whose signature has changed.
2. Always specify LANGUAGE plpgsql — PostgreSQL needs to know which language interpreter to use.
3. Validate inputs early. Check arguments before doing any DML; raise descriptive exceptions when something's wrong.
4. Use FOR UPDATE${nbsp}when you read-then-write on the same row, to lock it against concurrent modification.
5. Use INOUT parameters for outputs — procedures can't have OUT parameters, only IN and INOUT.
6. Use COMMIT inside procedures sparingly. Mid-procedure commits release locks and break transactional atomicity — they're a tool for batch processing, not the default.

• A stored procedure is a named PL/pgSQL block called via CALL. Introduced in PostgreSQL 11.
• Procedures don't return values — use INOUT parameters to hand data back to the caller.
• The big distinction from functions: procedures can COMMIT and ROLLBACK mid-execution. Functions can't.
• Parameters can be IN (default) or INOUT — not OUT.
• Use RETURN; (no expression) to exit early. Use RAISE EXCEPTION to abort and roll back.
• Reach for procedures for multi-step DML and batch jobs. Reach for functions for computations, lookups, and anything that returns data.