Stored Procedures

Stored procedures, added in PostgreSQL 11, are created with CREATE PROCEDURE and invoked with CALL rather than used inside a SELECT. They look similar to functions on the surface, but exist to solve a problem functions structurally can't.

Creating and calling a procedure

SQL

CREATE PROCEDURE transfer_funds(
  from_account integer,
  to_account integer,
  amount numeric
)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;
  COMMIT;
END;
$$;

CALL transfer_funds(101, 202, 50.00);

The key difference: transaction control

A PostgreSQL function always runs inside the transaction of the statement that called it — it cannot start, commit, or roll back a transaction itself. A procedure can, using COMMIT and ROLLBACK directly inside its own body, as shown above. This was the whole reason procedures were added to PostgreSQL: certain workflows — especially long-running batch jobs — genuinely need to commit intermediate progress rather than holding one giant transaction open for the entire run.

A batch procedure that commits progress

SQL

CREATE PROCEDURE archive_old_orders()
LANGUAGE plpgsql
AS $$
DECLARE
  batch_count integer;
BEGIN
  LOOP
    INSERT INTO orders_archive
      SELECT * FROM orders
      WHERE status = 'delivered' AND order_date < now() - interval '1 year'
      LIMIT 1000;

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

    DELETE FROM orders
      WHERE id IN (
        SELECT id FROM orders_archive
        WHERE archived_at IS NULL
        LIMIT 1000
      );

    COMMIT;  -- commit each batch instead of one huge transaction
  END LOOP;
END;
$$;

Note

Functions cannot do this. Calling COMMIT or ROLLBACK inside a CREATE FUNCTION body raises an error — a function is always a participant in whatever transaction is already running, never in control of it. This is the single most important practical distinction between the two.

Functions vs. procedures

	Function	Procedure
Defined with	`CREATE FUNCTION`	`CREATE PROCEDURE`
Invoked with	`SELECT` / used in an expression	`CALL`
Returns a value?	Yes (`RETURNS ...`)	No return value (can use `OUT` parameters instead)
Can `COMMIT`/`ROLLBACK` internally?	No	Yes
Typical use	Computations, lookups, transformations used within queries	Multi-step workflows, batch jobs, administrative tasks

When to reach for a procedure

If what you're writing needs to return a value to be used in a query, it's a function. If it's an operational task — a multi-step workflow, a batch process that should commit its progress incrementally, or administrative maintenance logic — reach for a procedure instead.

CREATE PROCEDURE + CALL (PostgreSQL 11+), as opposed to CREATE FUNCTION + SELECT.
Only procedures can COMMIT/ROLLBACK their own transaction internally — functions cannot.
Procedures don't return a value the way functions do; they can use OUT/INOUT parameters if needed.
Reach for a procedure for batch jobs and multi-step workflows; reach for a function for anything used inside a query.