PostgreSQL CREATE PROCEDURE: IN/OUT Parameters, CALL, Transactions, and Errors

The same four statements run in a fixed order after every import, from three places in your code, and one of them was never updated when a fifth statement was added. A procedure moves that sequence into the database, where it exists once. It is created from a name, a parameter list, a language and a body:

CREATE OR REPLACE PROCEDURE procedure_name(
    [argmode] argument_name data_type
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- procedure body
END;
$$;

The dollar signs are dollar quoting rather than a syntax of their own: the body is a string constant, and the $$ markers let it hold semicolons and quotes without escaping them.

The examples run on PostgreSQL 18 against two tables:

CREATE TABLE employees (
    id     INT PRIMARY KEY,
    name   TEXT NOT NULL,
    salary NUMERIC(10,2) NOT NULL
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    amount      NUMERIC(10,2) NOT NULL
);

INSERT INTO employees VALUES (1, 'Ada', 4000.00), (2, 'Grace', 5200.00);
INSERT INTO orders VALUES (10, 42, 99.90), (11, 42, 12.50), (12, 7, 40.00);

How a procedure differs from a function

A procedure is a named routine stored in the database. It takes parameters, runs SQL and PL/pgSQL statements, and is invoked as a statement of its own rather than inside an expression. That is the line between the two routine types in PostgreSQL: a function is called from SELECT, WHERE or a join, and a procedure is called with CALL.

Write a function when the result is the point, and a procedure when the action is the point, above all when the routine has to control transactions.

Both CREATE PROCEDURE and CALL arrived in PostgreSQL 11, whose release notes describe them as "SQL-level procedures, which can start and commit their own transactions". On an older server neither statement exists. plpgsql, the language every example here uses, is installed by default.

Writing a procedure and calling it

A procedure that raises one salary fills that skeleton in:

CREATE OR REPLACE PROCEDURE update_salary(
    IN p_employee_id INT,
    IN p_increase_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary + p_increase_amount
    WHERE id = p_employee_id;
END;
$$;

CALL update_salary(1, 500);
SELECT id, name, salary FROM employees ORDER BY id;

Write CREATE OR REPLACE from the start, because a procedure under development is deployed many times and the alternative is dropping it first, along with the privileges you granted on it.

A successful CALL of a procedure without output parameters prints only the command tag CALL, so the verification is a query of your own.

Four failures account for most first attempts:

• calling the procedure with SELECT, which fails because PostgreSQL expects a function in an expression, where the fix is CALL procedure_name(...)
• expecting a scalar RETURN, when the values a procedure hands back leave it through its OUT and INOUT parameters
• leaving the routine name unqualified, which resolves it through search_path instead of the schema you meant, where schema_name.procedure_name(...) is the fix
• calling without the privilege, since "the user must have EXECUTE privilege on the procedure in order to be allowed to invoke it", granted per signature

GRANT EXECUTE ON PROCEDURE update_salary(INT, NUMERIC) TO app_user;

IN, OUT, and INOUT parameters

An omitted argmode means IN, the fourth mode is VARIADIC, and a parameter that carries a default forces one on every parameter after it. The mode is the part that changes how the caller writes the CALL:

OUT depends on the server version: procedures were first allowed OUT parameters in PostgreSQL 14, and the PostgreSQL 13 reference says "OUT arguments are currently not supported for procedures. Use INOUT instead."

Two INOUT parameters carry a count and a sum out of one call:

CREATE OR REPLACE PROCEDURE get_order_totals(
    IN p_customer_id INT,
    INOUT p_order_count INT,
    INOUT p_total_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), COALESCE(SUM(amount), 0)
    INTO p_order_count, p_total_amount
    FROM orders
    WHERE customer_id = p_customer_id;
END;
$$;

CALL get_order_totals(42, NULL, NULL);

The two NULL arguments look redundant and are required: "arguments must be supplied for all procedure parameters that lack defaults, including OUT parameters", though "arguments matching OUT parameters are not evaluated, so it's customary to just write NULL for them". PostgreSQL returns the output parameters as a single row named after them, and a routine that has to hand back many rows is a set-returning function called from a FROM clause.

Transaction control inside a procedure

Transaction control is what procedures are for. Inside one you can COMMIT and start the next transaction, so a long operation lands in pieces:

CREATE OR REPLACE PROCEDURE raise_all_salaries(
    IN p_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT id FROM employees ORDER BY id LOOP
        UPDATE employees SET salary = salary + p_amount WHERE id = r.id;
        COMMIT;
    END LOOP;
END;
$$;

Each iteration commits its own row, and a failure in the middle leaves the rows already committed in place. The restriction is where the CALL itself sits. The CALL reference states it plainly: "If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Transaction control statements are only allowed if CALL is executed in its own transaction." Wrap the call in an explicit transaction and the COMMIT inside the loop fails:

BEGIN;
CALL raise_all_salaries(100);

ERROR:  invalid transaction termination

Two declarations take the ability away as well, and the CREATE PROCEDURE reference names both: a procedure declared SECURITY DEFINER, and one carrying a SET clause. The same error reaches you from an application, where the framework or the connection pool opens a transaction before your call, so check how the connection is configured before blaming the routine.

The language decides it too. Write the same body as LANGUAGE sql and the procedure is created without complaint; the CALL is where it stops, with an error that calls your procedure a function:

ERROR:  COMMIT is not allowed in an SQL function
CONTEXT:  SQL function "raise_all_salaries" during startup

Error handling with EXCEPTION

A procedure that moves money between two rows needs both statements to hold, and an EXCEPTION clause is how it reacts when one does not:

CREATE TABLE accounts (
    id      INT PRIMARY KEY,
    balance NUMERIC(10,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 500.00), (2, 100.00);

CREATE OR REPLACE PROCEDURE transfer_balance(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE id = p_from_account;

    UPDATE accounts
    SET balance = balance + p_amount
    WHERE id = p_to_account;
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'transfer of % from account % failed', p_amount, p_from_account;
END;
$$;

Ask it for more than the account holds, the check constraint rejects the debit, and the message you wrote is what the caller sees:

CALL transfer_balance(1, 2, 900.00);

ERROR:  transfer of 900.00 from account 1 failed

Both balances are still 500.00 and 100.00, and not because the procedure undid anything. The PL/pgSQL documentation on trapping errors explains the mechanism: "when an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back". Had the debit succeeded and the credit failed, the debit would have gone back with it.

A block with an EXCEPTION clause is also a subtransaction, so a COMMIT inside it is refused:

ERROR:  cannot commit while a subtransaction is active

Creating a procedure in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

A procedure usually lives outside the schema it belongs to, in a migration folder nobody diagrams. DbSchema keeps it with the model:

1. Connect through the PostgreSQL JDBC driver and let DbSchema reverse-engineer the database.
2. Read the routines in the Project Structure panel on the left, in the same tree as the tables they touch.
3. Paste a definition into the SQL Editor and run it with Run Script, which executes the whole editor content rather than the statement at the cursor.
4. Generate schema documentation so the routine reaches the people reviewing the data model.

Statements you run in the SQL Editor reach PostgreSQL as written; the reverse-engineered model and the diagrams are saved in the local .dbs file. PostgreSQL Triggers covers the routines that fire on their own.

Download DbSchema at https://dbschema.com/download.html, connect to your PostgreSQL database, and run one of the procedures above in the SQL Editor next to the diagram of the tables it changes. Connecting, reverse-engineering, the diagrams and the SQL Editor are in the free Community Edition; saving the model file and generating the documentation are in Pro.

Sources

1. PostgreSQL 18 documentation: CREATE PROCEDURE
2. PostgreSQL 18 documentation: CALL
3. PostgreSQL 18 documentation: Control Structures, trapping errors
4. PostgreSQL 18 documentation: Transaction Management in PL/pgSQL
5. PostgreSQL 18 documentation: Dollar-quoted string constants
6. PostgreSQL 14 release notes: OUT parameters for procedures
7. PostgreSQL 11 release notes: SQL-level procedures
8. DbSchema documentation: SQL Editor