PostgreSQL Function vs Procedure: Differences With Examples

The main difference between a function and a procedure in PostgreSQL is how you call them and what they can do with transactions. A function returns a value and runs inside a query, so you call it with SELECT and can use it in a WHERE clause. A procedure is run on its own with CALL, returns data only through output parameters, and can COMMIT or ROLLBACK part-way through its work. A function cannot.

That last point is the one that matters most in real systems. If you need to process a million rows in batches and commit after each batch, you need a procedure. If you need a calculation you can use inside queries, views or indexes, you need a function.

This tutorial shows both side by side on the same small table, with the real output and the real error messages from PostgreSQL 18, so you can see exactly where each one works and where it fails.

Function vs procedure: the short answer

Procedures arrived in PostgreSQL 11. Before that, everything was a function, which is why a lot of older code uses functions that return void for jobs that today would be procedures. The PostgreSQL documentation lists the differences, summarised here:

Together, functions and procedures are called routines. That is why ALTER ROUTINE and DROP ROUTINE work on both. There is no CREATE ROUTINE command, though.

Sample setup

Create a scratch database and an orders table. The examples were run in psql connected as the postgres user, with psql's default autocommit mode switched on.

CREATE TABLE orders (
  order_id    integer PRIMARY KEY,
  customer    text    NOT NULL,
  amount      numeric(10,2) NOT NULL,
  status      text    NOT NULL DEFAULT 'new',
  order_date  date    NOT NULL
);

INSERT INTO orders VALUES
  (1, 'Asha',  1200.00, 'shipped',   '2026-09-02'),
  (2, 'Ravi',   450.50, 'new',       '2026-09-15'),
  (3, 'Asha',   899.00, 'shipped',   '2026-10-01'),
  (4, 'Meena', 3150.00, 'cancelled', '2026-10-05'),
  (5, 'Ravi',  2000.00, 'shipped',   '2026-10-20');

CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);

The orders_archive table has the same columns, defaults and primary key as orders. We will use it later when a procedure moves old orders into it in batches.

Writing and using a function

Step 1: a scalar function you can use in any query

A scalar function takes arguments and returns one value. This one adds 18% GST to an amount:

CREATE FUNCTION order_total_with_gst(p_amount numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
RETURN round(p_amount * 1.18, 2);

The RETURN expression body is the SQL-standard function body form, available since PostgreSQL 14. PostgreSQL parses it immediately when you create the function and tracks which objects it depends on. The older form, a body written as a string between $ quotes, still works and is what PL/pgSQL needs.

IMMUTABLE tells the planner that the result depends only on the arguments: the same input always gives the same output, and the function never reads a table. Because a function is called inside a query, you can put it in the select list:

SELECT order_id, amount, order_total_with_gst(amount) AS total_with_gst
  FROM orders
 ORDER BY order_id;

 order_id | amount  | total_with_gst 
----------+---------+----------------
        1 | 1200.00 |        1416.00
        2 |  450.50 |         531.59
        3 |  899.00 |        1060.82
        4 | 3150.00 |        3717.00
        5 | 2000.00 |        2360.00
(5 rows)

And you can filter on it in a WHERE clause, which no procedure can do:

SELECT order_id, customer
  FROM orders
 WHERE order_total_with_gst(amount) > 2000;

 order_id | customer 
----------+----------
        4 | Meena
        5 | Ravi
(2 rows)

Step 2: a function that returns rows

A function can also return a whole result set. With RETURNS TABLE you name the output columns, and the caller treats the function like a table in the FROM clause:

CREATE FUNCTION customer_summary(p_customer text)
RETURNS TABLE (order_count bigint, total_spent numeric)
LANGUAGE sql
STABLE
AS $
  SELECT count(*), coalesce(sum(amount), 0)
    FROM orders
   WHERE customer = p_customer
     AND status <> 'cancelled';
$;

SELECT * FROM customer_summary('Asha');

 order_count | total_spent 
-------------+-------------
           2 |     2099.00
(1 row)

This one is STABLE, not IMMUTABLE, because it reads the orders table. STABLE means it does not modify the database and returns the same result for the same arguments within one statement, but the result can change between statements as the data changes. Returning rows that callers can join, filter and sort is something only a function can do.

Writing and calling a procedure

Step 3: a procedure with an OUT parameter

A procedure has no RETURNS clause. If it needs to send something back, it uses an OUT (or INOUT) parameter. This procedure cancels an order only if it is still new, and reports how many rows it changed:

CREATE PROCEDURE cancel_order(p_order_id integer, OUT p_rows integer)
LANGUAGE plpgsql
AS $
BEGIN
  UPDATE orders
     SET status = 'cancelled'
   WHERE order_id = p_order_id
     AND status = 'new';
  GET DIAGNOSTICS p_rows = ROW_COUNT;
END;
$;

GET DIAGNOSTICS p_rows = ROW_COUNT copies the number of rows touched by the last SQL statement into the output parameter. Now run it with CALL:

CALL cancel_order(2, NULL);

 p_rows 
--------
      1
(1 row)

Order 2 was new, so one row changed. Run the same call again and the result is 0, because the order is already cancelled:

CALL cancel_order(2, NULL);

 p_rows 
--------
      0
(1 row)

Notice the NULL in the second position. When you use CALL from plain SQL, you must pass an argument for every parameter that has no default, including OUT parameters. PostgreSQL does not evaluate the value you pass for an OUT parameter, so the custom is to write NULL. The output values come back as a result row.

Step 4: calling a procedure from PL/pgSQL

Inside PL/pgSQL (a function, a procedure or a DO block), CALL works differently: each OUT or INOUT parameter must be matched by a variable, and PostgreSQL writes the result back into that variable after the procedure finishes.

DO $
DECLARE
  v_rows integer;
BEGIN
  CALL cancel_order(5, v_rows);
  RAISE NOTICE 'rows changed: %', v_rows;
END;
$;

NOTICE:  rows changed: 0
DO

Order 5 is already shipped, so the procedure's WHERE clause matched nothing and the variable holds 0.

The real difference: COMMIT inside a procedure

Everything a function does is part of the statement that called it. If the calling statement or its transaction rolls back, the function's work rolls back too. That is usually what you want, but it is a problem for long batch jobs: one huge transaction holds row locks for a long time, keeps old row versions from being cleaned up, and loses all its work if it fails near the end.

A procedure can end the current transaction with COMMIT and carry on. PostgreSQL starts a new transaction automatically, so you do not write START TRANSACTION. Think of it like saving a long document every few pages instead of only at the end.

Step 5: archive old orders in committed batches

This procedure moves orders older than a date into orders_archive, a small batch at a time, and commits after each batch:

CREATE PROCEDURE archive_orders(p_before date, p_batch integer DEFAULT 2)
LANGUAGE plpgsql
AS $
DECLARE
  v_moved integer;
BEGIN
  LOOP
    WITH moved AS (
      DELETE FROM orders
       WHERE order_id IN (SELECT order_id
                            FROM orders
                           WHERE order_date < p_before
                           ORDER BY order_id
                           LIMIT p_batch)
      RETURNING *
    )
    INSERT INTO orders_archive SELECT * FROM moved;

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

    COMMIT;
    RAISE NOTICE 'committed a batch of % rows', v_moved;
  END LOOP;
END;
$;

The WITH moved AS (DELETE ... RETURNING *) part deletes a batch and hands the deleted rows to the INSERT in the same statement, so a row is never lost between the two tables. The batch size is 2 here only so you can see several commits on five rows; in production you would use something like a few thousand.

CALL archive_orders('2026-10-10');

NOTICE:  committed a batch of 2 rows
NOTICE:  committed a batch of 2 rows
CALL

Two batches of two rows were moved and committed. Check both tables:

SELECT order_id, customer, order_date FROM orders_archive ORDER BY order_id;
SELECT order_id, customer, order_date FROM orders ORDER BY order_id;

 order_id | customer | order_date 
----------+----------+------------
        1 | Asha     | 2026-09-02
        2 | Ravi     | 2026-09-15
        3 | Asha     | 2026-10-01
        4 | Meena    | 2026-10-05
(4 rows)

 order_id | customer | order_date 
----------+----------+------------
        5 | Ravi     | 2026-10-20
(1 row)

The four orders from before 10 October are in the archive, and only order 5 is left. If the job had failed during a later batch, the earlier committed batches would have stayed in place, and you could simply run the CALL again to finish the rest.

Step 6: the same COMMIT in a function fails

Try the same idea as a function:

CREATE FUNCTION archive_orders_fn(p_before date)
RETURNS integer
LANGUAGE plpgsql
AS $
DECLARE
  v_moved integer;
BEGIN
  DELETE FROM orders WHERE order_date < p_before;
  GET DIAGNOSTICS v_moved = ROW_COUNT;
  COMMIT;
  RETURN v_moved;
END;
$;

SELECT archive_orders_fn('2026-10-10');

CREATE FUNCTION
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function archive_orders_fn(date) line 7 at COMMIT

PostgreSQL accepts the function when you create it, because PL/pgSQL code is checked only for syntax at that point. The error appears when the COMMIT actually runs. The whole SELECT fails, and the DELETE before the COMMIT is rolled back with it.

Step 7: CALL inside BEGIN ... COMMIT also blocks transaction control

A procedure can only commit when the CALL runs in its own transaction. If you wrap the CALL in an explicit transaction block, you get the same error:

BEGIN;
CALL archive_orders('2026-12-31');
ROLLBACK;

BEGIN
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function archive_orders(date,integer) line 20 at COMMIT
ROLLBACK

This catches many people, because some client tools and drivers open a transaction for you without asking. If your application framework always wraps statements in a transaction, a procedure that commits will fail there. Run such procedures with autocommit on, for example from psql or a scheduled job, or turn autocommit on for that connection.

Transaction control is also allowed only when the chain of calls above the procedure is made of CALL or DO only. If a procedure is called from a function that was called from SELECT, the SELECT is in the way and COMMIT fails.

Common mistakes and their error messages

Calling a function with CALL, or a procedure with SELECT

CALL order_total_with_gst(100);

ERROR:  order_total_with_gst(integer) is not a procedure
LINE 1: CALL order_total_with_gst(100);
             ^
HINT:  To call a function, use SELECT.

The hint tells you the fix. Going the other way, a procedure is not found as a function at all, so the message is less helpful:

SELECT cancel_order(2, NULL);

ERROR:  function cancel_order(integer, unknown) does not exist
LINE 1: SELECT cancel_order(2, NULL);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

If you see "function ... does not exist" for something you know you created, check whether it is a procedure. In psql, \df shows a Type column with func or proc (see the FAQ below).

Leaving out the OUT argument in CALL

CALL cancel_order(2);

ERROR:  procedure cancel_order(integer) does not exist
LINE 1: CALL cancel_order(2);
             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.

In plain SQL, CALL needs a value for the OUT parameter too. Pass NULL: CALL cancel_order(2, NULL);

Returning a value from a procedure

CREATE PROCEDURE bad_proc()
LANGUAGE plpgsql
AS $
BEGIN
  RETURN 1;
END;
$;

ERROR:  RETURN cannot have a parameter in a procedure
LINE 5:   RETURN 1;
                 ^

A procedure can use RETURN with no value to exit early. To send a value back, declare an OUT parameter and assign to it.

COMMIT in a SECURITY DEFINER procedure

A procedure that runs with its owner's privileges (SECURITY DEFINER) cannot run transaction control statements. The same is true for a procedure with a SET clause attached, such as SET search_path = ... . This is documented, and it fails at run time:

ALTER PROCEDURE archive_orders(date, integer) SECURITY DEFINER;
CALL archive_orders('2026-12-31');

ALTER PROCEDURE
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function archive_orders(date,integer) line 20 at COMMIT

Undo it with ALTER PROCEDURE archive_orders(date, integer) SECURITY INVOKER;. If a batch procedure must run with extra privileges, grant the needed table privileges to the role that runs the job instead. Privileges are covered in PostgreSQL DBA Part 7: Roles, Users and Privileges.

Using a non-IMMUTABLE function in an index

An expression index can only use IMMUTABLE functions. A function created without a volatility label is VOLATILE by default, so this fails:

CREATE FUNCTION order_month(p_date date)
RETURNS text
LANGUAGE sql
RETURN to_char(p_date, 'YYYY-MM');

CREATE INDEX orders_month_idx ON orders (order_month(order_date));

CREATE FUNCTION
ERROR:  functions in index expression must be marked IMMUTABLE

Do not fix this by simply adding IMMUTABLE. The documentation warns that labelling a function IMMUTABLE when its result depends on a configuration setting, such as the time zone, is a common error that can give stale or wrong results. Mark a function IMMUTABLE only when its output truly depends on nothing but its arguments.

When to use a function and when to use a procedure

• Use a function for calculations, lookups and anything you want inside SELECT, WHERE, JOIN, views, CHECK constraints, defaults, triggers or index expressions. Trigger code in PostgreSQL is always a function (RETURNS trigger).
• Use a function when the caller needs a set of rows to join or filter (RETURNS TABLE or RETURNS SETOF).
• Use a procedure for batch jobs that should commit as they go: archiving, purging, backfilling a new column, moving data between tables.
• Use a procedure for an action that does not belong in a query and only reports a status, such as "cancel this order", especially when porting Oracle procedures.
• Either works for a single unit of work with no COMMIT. In that case pick the one that matches how it will be called.

For job queues where several workers process rows at the same time, combine a batch procedure with row locking that skips busy rows, as shown in PostgreSQL FOR UPDATE SKIP LOCKED: Build a Safe Job Queue.

Frequently asked questions

Can a PostgreSQL function return a value and a procedure not?

Yes. A function declares its result with RETURNS. A procedure has no return value, but it can send values back through OUT or INOUT parameters, which CALL shows as a result row.

Can a PostgreSQL function do INSERT, UPDATE or DELETE?

Yes. A VOLATILE function (the default) can change data. What it cannot do is COMMIT or ROLLBACK. Its changes are committed or rolled back together with the statement that called it.

How do I tell if a routine is a function or a procedure?

In psql, run \df. The Type column shows func or proc:

                                                           List of functions
 Schema |         Name         |                Result data type                |              Argument data types               | Type 
--------+----------------------+------------------------------------------------+------------------------------------------------+------
 public | archive_orders       |                                                | IN p_before date, IN p_batch integer DEFAULT 2 | proc
 public | archive_orders_fn    | integer                                        | p_before date                                  | func
 public | cancel_order         |                                                | IN p_order_id integer, OUT p_rows integer      | proc
 public | customer_summary     | TABLE(order_count bigint, total_spent numeric) | p_customer text                                | func
 public | order_month          | text                                           | p_date date                                    | func
 public | order_total_with_gst | numeric                                        | p_amount numeric                               | func
(6 rows)

In SQL, the prokind column of pg_proc holds f for a function and p for a procedure, and provolatile holds i, s or v for IMMUTABLE, STABLE and VOLATILE:

SELECT proname, prokind, provolatile
  FROM pg_proc
 WHERE pronamespace = 'public'::regnamespace
 ORDER BY proname;

       proname        | prokind | provolatile 
----------------------+---------+-------------
 archive_orders       | p       | v
 archive_orders_fn    | f       | v
 cancel_order         | p       | v
 customer_summary     | f       | s
 order_month          | f       | v
 order_total_with_gst | f       | i
(6 rows)

Can I call a procedure from a function?

Yes, a function can run CALL. But the procedure cannot COMMIT or ROLLBACK in that case, because the function's own calling query sits between it and the top level.

Is a procedure faster than a function in PostgreSQL?

No. Neither is faster by nature. Choose by what the routine has to do: return a value to a query (function) or control transactions (procedure).

Summary

• A function returns a value and is used inside queries with SELECT; a procedure is run with CALL.
• A procedure returns data only through OUT or INOUT parameters (OUT since PostgreSQL 14); in plain SQL pass NULL for OUT arguments.
• Only a procedure can COMMIT or ROLLBACK, and only when CALL is not inside BEGIN ... COMMIT and not under a SELECT.
• COMMIT in a function, in a SECURITY DEFINER procedure or in a procedure with a SET clause fails with "invalid transaction termination".
• Use IMMUTABLE, STABLE or VOLATILE correctly on functions; only IMMUTABLE functions can be used in index expressions.
• Use procedures for batch jobs that commit as they go, and functions for everything that belongs in a query.