SQL Stored Procedures vs Functions: Choosing Wisely

What and Why

Both stored procedures and user-defined functions let you store SQL (and procedural code) in the database and invoke it by name. They feel similar but solve different problems. Procedures are for doing work with side effects and transaction control. Functions are for computing values inside queries.

Picking well affects how clean your data layer is, how predictable your transactions behave, and whether your migrations turn into spaghetti. Picking poorly leads to functions with hidden side effects and procedures abused for things SELECT could do.

Mental Model

FUNCTION
- returns a value or table
- invoked inside SQL:  SELECT f(x) FROM ...
- cannot manage transactions
- usually side-effect free

PROCEDURE
- returns nothing (or out params)
- invoked imperatively:  CALL p(x)
- can BEGIN / COMMIT / ROLLBACK
- often has side effects (writes, audit, jobs)

In Postgres, procedures were added in version 11 specifically to allow transaction control inside server-side code. Before that, all you had was FUNCTION, and people abused it.

Hands-on Example

A scalar function that computes shipping cost is fine inside queries:

CREATE OR REPLACE FUNCTION shipping_cost(weight_kg numeric, country text)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT CASE country
           WHEN 'US' THEN 5 + weight_kg * 1.5
           WHEN 'EU' THEN 7 + weight_kg * 1.8
           ELSE         12 + weight_kg * 2.2
         END;
$$;

SELECT id, shipping_cost(weight_kg, country) AS ship
FROM   orders
WHERE  status = 'pending';

A table-returning function for reuse in queries:

CREATE OR REPLACE FUNCTION top_customers(limit_n int)
RETURNS TABLE (customer_id bigint, revenue numeric)
LANGUAGE sql
STABLE
AS $$
  SELECT customer_id, SUM(total)
  FROM   orders
  GROUP  BY customer_id
  ORDER  BY 2 DESC
  LIMIT  limit_n;
$$;

SELECT * FROM top_customers(10);

A procedure for a multi-step operation that needs its own commits:

CREATE OR REPLACE PROCEDURE settle_day(p_day date)
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN
    SELECT id FROM orders WHERE sold_at = p_day AND status = 'shipped'
  LOOP
    UPDATE orders SET status = 'settled' WHERE id = rec.id;
    INSERT INTO settlement_audit(order_id, settled_at) VALUES (rec.id, now());
    COMMIT;     -- only legal inside a procedure
  END LOOP;
END;
$$;

CALL settle_day(DATE '2026-06-27');

The procedure chunks work into many small transactions to avoid one giant lock. A function could not do that.

Common Pitfalls

• Side effects in functions. A function that inserts a row may be called any number of times by the planner. Use a procedure for writes.
• Wrong volatility category. Marking a non-deterministic function IMMUTABLE lets the planner cache its result and produce wrong answers. Use STABLE for read-only queries and VOLATILE (default) when in doubt.
• SECURITY DEFINER traps. A function that runs with the owner’s privileges can leak access. Set search_path explicitly and avoid SECURITY DEFINER unless you really need it.
• Procedures in MySQL vs Postgres differ. MySQL has had procedures forever but no transaction control inside them in older versions. Read your engine’s docs.
• Heavy logic in stored procs without tests. Database code is just code. Untested SQL functions rot the same way untested services do.

Production Tips

• Use migrations (Flyway, Sqitch, Atlas) to version stored procedures and functions alongside schema. Drift between environments is the usual disaster.
• Keep business rules in application code by default. Push to the database only when round trips, atomicity, or data locality demand it.
• Write unit tests for non-trivial functions using pgTAP or a containerized DB and an assertion framework. Reviews catch syntax; tests catch semantics.
• Choose LANGUAGE sql over LANGUAGE plpgsql when possible. SQL functions can be inlined by the planner; plpgsql cannot.
• Document inputs, outputs, and side effects in a comment block above each function. Stored code is “spooky action at a distance” by default; documentation lifts the fog.

Wrap-up

Reach for a function to compute values inside queries: pure, deterministic, embedded in SQL. Reach for a procedure to drive multi-step work that may need its own transactions: batch settlement, data migrations, scheduled maintenance. Keep them tested, version-controlled, and minimal, and the database becomes a place for the work that genuinely belongs there.