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

A procedure is a set of SQL statements that PostgreSQL stores under a name and runs when you call it. For example, you can store the UPDATE that adds money to an account, and run it from any application with one CALL.

Every procedure has the same shape:

CREATE [OR REPLACE] PROCEDURE name(parameter type, ...)
LANGUAGE plpgsql
AS $$
BEGIN
  statements;
END;
$$;

CALL name(argument, ...);

• OR REPLACE overwrites a procedure that already exists with this name and these parameter types. Change a parameter type, and PostgreSQL creates a second procedure of the same name instead.
• parameter type declares one value that the procedure takes, such as p_id int. A mode before the name says which way the value goes: IN, OUT or INOUT. Without a mode, the parameter is IN.
• LANGUAGE plpgsql writes the body in PL/pgSQL, PostgreSQL's procedural language: SQL with variables, loops and error handling. PL/pgSQL is installed by default.
• $$ ... $$ encloses the body as a string, a form called dollar quoting. Single quotes inside the body need no escaping.
• BEGIN ... END holds the statements that run on each call.
• CALL runs the procedure, with one argument for each parameter.

Create a sample table of three accounts:

CREATE TABLE accounts (
  id      int PRIMARY KEY,
  owner   text NOT NULL,
  balance int NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts VALUES
  (1, 'Anna', 500),
  (2, 'Ben',  100),
  (3, 'Cleo', 0);

The CHECK constraint keeps every balance at 0 or more. Procedures arrived in PostgreSQL 11. Every statement in this article was run on PostgreSQL 18.6, in order, so each example starts from the rows that the previous one left.

CREATE PROCEDURE and CALL

CREATE PROCEDURE stores a procedure in the database, and CALL runs it with the arguments you pass. For example, a procedure named deposit can add an amount to one account's balance.

CREATE PROCEDURE deposit(p_id int, p_amount int)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_id;
END;
$$;

CALL deposit(2, 50);

SELECT * FROM accounts ORDER BY id;

CALL ran the UPDATE with p_id set to 2 and p_amount set to 50. Ben's balance went from 100 to 150. For a procedure without output parameters, psql prints only the word CALL, so the SELECT shows the change.

IN, OUT and INOUT parameters

An OUT parameter returns a value from the procedure to the caller. For example, a procedure can return the balance of one account.

CREATE PROCEDURE get_balance(IN p_id int, OUT p_balance int)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT balance INTO p_balance FROM accounts WHERE id = p_id;
END;
$$;

CALL get_balance(2, NULL);

SELECT ... INTO copied Ben's balance into p_balance. CALL returned the OUT parameters as one row, with a column named after each parameter.

The NULL holds the place of the OUT parameter. CALL needs an argument for every parameter, but PostgreSQL ignores the argument of an OUT parameter.

An INOUT parameter works in both directions. The caller passes a value in, and CALL returns the parameter's final value in its result row, like an OUT parameter.

Procedures accept OUT parameters from PostgreSQL 14 on. On PostgreSQL 13, get_balance fails, and the hint names the workaround, an INOUT parameter:

ERROR:  procedures cannot have OUT arguments
HINT:  INOUT arguments are permitted.

COMMIT inside a procedure

COMMIT inside a procedure saves the changes made so far, and the procedure goes on in a new transaction. For example, a procedure can charge a fee to every account and commit after each one.

CREATE PROCEDURE charge_fee(p_fee int)
LANGUAGE plpgsql
AS $$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT id FROM accounts ORDER BY id LOOP
    UPDATE accounts SET balance = balance - p_fee WHERE id = r.id;
    COMMIT;
  END LOOP;
END;
$$;

CALL charge_fee(20);

The CALL fails on Cleo's account:

ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (3, Cleo, -20).

SELECT * FROM accounts ORDER BY id;

The FOR loop runs the UPDATE once for each account, in the order of id. r, declared under DECLARE, holds the current account's row. COMMIT saved Anna's fee and Ben's fee before the loop reached Cleo.

Cleo's balance can't go below 0, so her UPDATE failed. The error rolled back only the last transaction, which held Cleo's fee. Without the COMMIT, all three UPDATE statements run in one transaction, and the error rolls back Anna's and Ben's fees too.

EXCEPTION

An EXCEPTION clause catches an error raised in its block, and runs the statements you write for that error. For example, a transfer procedure can replace a constraint error with a message that names the account.

CREATE PROCEDURE transfer(p_from int, p_to int, p_amount int)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
EXCEPTION
  WHEN check_violation THEN
    RAISE EXCEPTION 'account % has less than %', p_from, p_amount;
END;
$$;

CALL transfer(2, 1, 200);

ERROR:  account 2 has less than 200

Ben has 130, so taking 200 from his account broke the CHECK constraint. check_violation is PostgreSQL's name for that error, so the WHEN branch caught the error. RAISE EXCEPTION then raised a new error, with your message in place of the constraint's.

RAISE replaces each % in the message with the next value in its list, here p_from and then p_amount. Nothing changed: the debit failed, and the credit never ran. Ben still has 130, and Anna 480.

What goes wrong

Calling a procedure with SELECT

SELECT runs functions, and PostgreSQL refuses to run a procedure with it:

SELECT deposit(1, 10);

ERROR:  deposit(integer, integer) is a procedure
HINT:  To call a procedure, use CALL.

A function returns a value, and a query uses that value. A procedure runs as a statement of its own:

COMMIT after BEGIN

A procedure can commit only when its CALL is a statement of its own, outside a transaction that you opened with BEGIN. Run charge_fee after BEGIN, and its COMMIT fails:

BEGIN;
CALL charge_fee(20);

ERROR:  invalid transaction termination

A connection with autocommit turned off opens a transaction before the CALL, and gets the same error.

Two clauses of CREATE PROCEDURE take COMMIT away as well: SECURITY DEFINER, which runs the procedure with its owner's privileges, and SET, which changes a setting during the call. A procedure with either clause fails at COMMIT with the same error.

COMMIT inside an EXCEPTION block

PostgreSQL runs a block that has an EXCEPTION clause as a subtransaction, a part of a transaction that PostgreSQL can roll back alone. A COMMIT inside the subtransaction can't end the transaction around it. Add COMMIT; between the two UPDATE statements of transfer, and the CALL fails:

ERROR:  cannot commit while a subtransaction is active

Transaction management in PL/pgSQL states this rule. Keep the COMMIT in a block that has no EXCEPTION clause.

Run the procedures in DbSchema

The SQL Editor in DbSchema runs a statement and shows its result in a grid under the editor. Press Editor in the toolbar and choose SQL Editor. Paste a CREATE PROCEDURE statement from this article, select the whole statement, and press Run.

Download DbSchema at https://dbschema.com/download.html and connect it to your PostgreSQL database. Create the accounts table in the SQL Editor, then create each procedure and call it. The free Community Edition includes the database connection and the SQL Editor.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download