Functions compute a value and hand it back to a query. A procedure is different: it runs for its side effects, returns nothing to a surrounding SELECT, and is invoked with its own statement — CALL. That distinction unlocks the one thing a function can never do: manage transactions from the inside, committing work as it goes.
The seed is a work queue: a jobs table with 500 rows all marked pending, plus an empty archived_jobs table we'll fill later.
SELECT status, count(*) FROM jobs GROUP BY status;
CREATE PROCEDURE and CALL
A procedure looks like a function without a RETURNS clause. Here's one that marks every pending job as done. Define it first:
CREATE PROCEDURE mark_all_done()
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE jobs SET status = 'done' WHERE status = 'pending';
END;
$$;
You can't SELECT mark_all_done() — a procedure isn't an expression. You invoke it with CALL, which is a statement all on its own:
CALL mark_all_done();
Check the result — everything is done now:
SELECT status, count(*) FROM jobs GROUP BY status;
So far this is just a function you call awkwardly. The real reason procedures exist is the next part. Let's reset the queue before moving on:
UPDATE jobs SET status = 'pending';
Procedures can control transactions
Inside a function, the whole call runs in one transaction that the caller owns — a function cannot COMMIT or ROLLBACK. A procedure can. That's the headline feature.
Why does it matter? Imagine processing all 500 jobs in a single UPDATE. It works, but it's one giant transaction: it holds row locks on every touched row until the very end, and if it fails at row 499 you lose all the work. Batch and maintenance jobs want the opposite — process a chunk, commit it, release those locks, move on. A crash then costs you one chunk, not the lot.