Right, let’s settle this. You’re staring at your SQL client, about to automate something, and you hit the eternal question: FUNCTION or PROCEDURE? The difference seems pedantic until you pick the wrong one and your entire transaction logic goes sideways. I’m here to make sure that doesn’t happen.

The core of the confusion is that for decades, PostgreSQL only had FUNCTION. Procedures were a later addition (shipped in PostgreSQL 11) to bring us in line with the SQL standard and, frankly, to handle a specific job that functions were awkwardly faking. The simplest way to think about it is this: A FUNCTION returns a result. A PROCEDURE does not. But of course, it’s PostgreSQL, so that simple answer is just the doorway to a much more interesting rabbit hole.

The Transaction Control Superpower (And Its Dangers)

This is the big one, the game-changer, and the reason you can’t just blindly replace one with the other. Functions run inside a transaction. Procedures control transactions.

Think of a function like a trusted specialist you bring into your workshop. They can use all your tools (the database tables), they can make a big mess, but they can’t decide to permanently install (COMMIT) or throw away (ROLLBACK) the entire workshop. Only you, the transaction boss, can do that.

A procedure, on the other hand, is you handing the keys to the workshop to a new foreman. They can start work (COMMIT), decide a project is unsalvageable (ROLLBACK), and even section off part of the workshop to experiment without affecting the main project (SAVEPOINT).

This is both incredibly powerful and incredibly dangerous. Let’s see it in action.

-- This will FAIL. You can't call COMMIT inside a function.
CREATE OR REPLACE FUNCTION naughty_function()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (message) VALUES ('I’m about to break the rules');
    COMMIT; -- Nope. Not allowed.
END;
$$;

-- This is perfectly legal and expected inside a procedure.
CREATE OR REPLACE PROCEDURE responsible_procedure()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (message) VALUES ('Starting a new unit of work.');
    COMMIT; -- The procedure is in charge now.

    INSERT INTO audit_log (message) VALUES ('This is after the commit.');
    -- This second insert is in a new transaction!
END;
$$;

The inability to control transactions inside a function is a safety feature. It makes them predictable, composable building blocks. You can call five functions inside a single transaction, and if the last one fails, a single ROLLBACK undoes the work of all of them. Procedures break this composability. You call one procedure, and it might commit three times internally. You can’t roll back its work after the fact. This is why procedures are ideal for “top-level” operational tasks like data migrations, bulk loading, or complex administrative work where you want to commit in chunks to avoid massive rollback segments.

The Return(s) Situation

My initial simplification holds true here: functions must return something, even if it’s just void. Procedures cannot return anything. But wait, there’s more! Functions can return a single value, a set of values (SETOF), or even a table (TABLE (...)). They are designed to be used in a SELECT statement.

-- A function that returns a value. You'd use it in a SELECT.
CREATE OR REPLACE FUNCTION get_customer_name(customer_id int)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
    customer_name text;
BEGIN
    SELECT name INTO customer_name FROM customers WHERE id = customer_id;
    RETURN customer_name;
END;
$$;

SELECT get_customer_name(123); -- Perfectly valid

Procedures, since they don’t return anything, are called with CALL. They are a statement unto themselves, not part of a query.

-- A procedure that does a thing. You CALL it.
CREATE OR REPLACE PROCEDURE archive_old_customers(retention_period interval)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO customers_archive
    SELECT * FROM customers WHERE created_at < NOW() - retention_period;

    DELETE FROM customers WHERE created_at < NOW() - retention_period;
    -- You might even put a COMMIT in here after the archive, but before the delete.
END;
$$;

CALL archive_old_customers('5 years');

The designers made a questionable choice here with OUT parameters, just to keep us on our toes. Both functions and procedures can have OUT parameters. For a procedure, these are the only way to “return” data. It’s a side-channel.

-- A procedure using OUT parameters to send data back
CREATE OR REPLACE PROCEDURE get_customer_stats(
    OUT total_count bigint,
    OUT active_count bigint
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO total_count FROM customers;
    SELECT COUNT(*) INTO active_count FROM customers WHERE is_active = true;
END;
$$;

-- You have to CALL it and then reference the named parameters.
CALL get_customer_stats(null, null); -- The nulls are a weird placeholder
-- The results are now in the OUT parameters, which is clunky.

-- A function can do the same thing, but can also be used in a SELECT.
CREATE OR REPLACE FUNCTION get_customer_stats_func()
RETURNS TABLE(total_count bigint, active_count bigint)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(*) AS total_count,
        COUNT(*) FILTER (WHERE is_active = true) AS active_count
    FROM customers;
END;
$$;

-- This is far more elegant and SQL-like.
SELECT * FROM get_customer_stats_func();

See? Using a function with RETURNS TABLE is almost always cleaner than a procedure with OUT parameters if your goal is to get a result set back.

So, Which One Do I Use? A Heuristic.

Stop thinking about them as interchangeable. They are different tools.

Use a FUNCTION when:

  • You need to return a value or a set of rows (especially inside a SELECT).
  • You want the behavior to be atomic and composable within a larger transaction.
  • You’re doing a calculation or transformation on data.
  • You’re creating a trigger. (Triggers must be functions, another one of those historical artifacts).

Use a PROCEDURE when:

  • You are performing an action that has distinct, committable steps (e.g., “archive these records, then delete them, and commit after the archive in case the delete fails”).
  • You are writing a script-like operation where transaction control is the entire point.
  • You don’t need to return a result, only success/failure (which is signaled by raising an exception).

The pitfall is using a procedure where you should have used a function. You’ll lose the ability to nest it safely inside other transactions. The other pitfall is trying to force a function to do a procedure’s job by using PRAGMA AUTONOMOUS_TRANSACTION or other hacks. If you find yourself doing that, just use a procedure.

In short: need a result and transactional safety? Function. Need to be the boss of transactions? Procedure. It’s really that simple once you peel back the layers of legacy and standardese. Now go build something without worrying about it.