A function calculates a value. A performs an action: one change or several changes that belong together.
Create a procedure
Shipping an order means two steps: set the status, and send a message.
sql
CREATE PROCEDURE ship_order(o integer)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE orders SET status = 'shipped' WHERE id = o AND status = 'pending';
RAISE NOTICE 'order % shipped', o;
END;
$$;It is the same shape as a PL/pgSQL function, with two differences: PROCEDURE in place of FUNCTION, and no RETURNS line.
Run it with CALL
sql
CALL ship_order(113);A procedure is not a value, so a SELECT of it fails:
text
ERROR: ship_order(integer) is a procedure
HINT: To call a procedure, use CALL.Procedure or function
| Function | Procedure | |
|---|---|---|
| Returns a value | yes, RETURNS type | no |
| How you run it | inside a query: SELECT f(…) | alone: CALL p(…) |
| Typical job | calculate, look up | change data in several steps |
A function can change data too, but a SELECT that changes data is hard to follow. Use a procedure for actions. A procedure can also end a transaction part way. Chapter 14 explains transactions.