Stored Functions & Procedures in PL/pgSQL

Move twelve round-trips into one transactional procedure — and prove a failed order leaves no partial damage behind

A PL/pgSQL FUNCTION and a PROCEDURE look almost identical on the page — both have DECLARE, BEGIN, variables, loops — but they differ in exactly the way that matters for an order-processing operation: a function always runs inside whatever transaction the caller already has open, with no COMMIT or ROLLBACK of its own allowed anywhere inside it. A procedure, called with CALL instead of SELECT, can manage its own transaction boundaries completely.\n\nThis lab builds process_order as a real procedure: it validates the order exists, decrements stock for every line item, and logs the event — and if any single line item does not have enough stock, the entire operation aborts with zero partial changes, not a half-decremented mess. That all-or-nothing guarantee is proven directly here, not just described: an order that fails partway through is checked afterward to confirm nothing at all was actually changed.

A Function: Runs Inside the Caller's Transaction

CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric) RETURNS numeric
LANGUAGE plpgsql AS $
BEGIN
  RETURN a / b;
EXCEPTION WHEN division_by_zero THEN
  RAISE NOTICE 'division by zero caught, returning NULL';
  RETURN NULL;
END;
$;

A function can 't COMMIT or ROLLBACK — whatever transaction called it is still the one in charge when the function returns.

A Procedure: Manages Its Own Transaction

CREATE OR REPLACE PROCEDURE process_order(p_order_id int)
LANGUAGE plpgsql AS $
DECLARE r record;
BEGIN
  IF NOT EXISTS (SELECT 1 FROM order_items WHERE order_id = p_order_id) THEN
    RAISE EXCEPTION 'order % does not exist', p_order_id;
  END IF;

  FOR r IN SELECT product_id, qty FROM order_items WHERE order_id = p_order_id LOOP
    UPDATE stock SET qty = qty - r.qty WHERE product_id = r.product_id AND qty >= r.qty;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'insufficient stock for product %', r.product_id;
    END IF;
  END LOOP;

  INSERT INTO event_log (msg) VALUES ('order ' || p_order_id || ' processed');
  COMMIT;
END;
$;

CALL process_order(1);

Called with CALL, not SELECT — procedures are not expressions, they are statements in their own right.

Why NOT FOUND Is the Right Check Here

UPDATE ... WHERE product_id = r.product_id AND qty >= r.qty either updates exactly one row (there was enough stock) or zero rows (there was not) — it can never fail with an error either way. NOT FOUND after an UPDATE is PL/pgSQL's way of asking "did that statement actually touch a row," which is exactly the signal needed to notice zero rows changed and raise a real exception instead of silently proceeding as if the decrement had succeeded.

Function vs Procedure

A FUNCTION is called as part of an expression (SELECT my_func(...)) and always executes inside whatever transaction is already open — it cannot COMMIT or ROLLBACK. A PROCEDURE is called as its own statement (CALL my_proc(...)) and, when it has no OUT parameters and is called outside an existing multi-statement transaction, can manage transaction boundaries directly with COMMIT and ROLLBACK inside its own body.

RAISE EXCEPTION and Automatic Rollback

An uncaught RAISE EXCEPTION inside a function or procedure propagates up like any PostgreSQL error, aborting the entire current transaction unless something further up the call chain specifically catches it with EXCEPTION WHEN. Every change made earlier in that same transaction — including earlier iterations of a loop that already succeeded — is rolled back along with it, which is exactly what gives a multi-step procedure its all-or-nothing guarantee.

🧮 Write a Function With EXCEPTION WHEN

Write a function that returns a value and catches a specific error inside itself instead of letting it propagate.

psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric) RETURNS numeric LANGUAGE plpgsql AS \$\$ BEGIN RETURN a / b; EXCEPTION WHEN division_by_zero THEN RAISE NOTICE 'division by zero caught, returning NULL'; RETURN NULL; END; \$\$;"

psql -U postgres -d beer_db -c "SELECT safe_divide(10,2);"

psql -U postgres -d beer_db -c "SELECT safe_divide(10,0);"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric) RETURNS numeric LANGUAGE plpgsql AS \$\$ BEGIN RETURN a / b; EXCEPTION WHEN division_by_zero THEN RAISE NOTICE 'division by zero caught, returning NULL'; RETURN NULL; END; \$\$;" SET CREATE FUNCTION student@lab:~$ psql -U postgres -d beer_db -c "SELECT safe_divide(10,2);" SET safe_divide -------------------- 5.0000000000000000 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT safe_divide(10,0);" SET NOTICE: division by zero caught, returning NULL safe_divide ------------- (1 row)

📦 Write the Order-Processing Procedure

Write process_order as a real PROCEDURE: validate the order exists, decrement stock for every item, log the event, and commit.

psql -U postgres -d beer_db -c "CREATE OR REPLACE PROCEDURE process_order(p_order_id int) LANGUAGE plpgsql AS \$\$ DECLARE r record; BEGIN IF NOT EXISTS (SELECT 1 FROM order_items WHERE order_id = p_order_id) THEN RAISE EXCEPTION 'order % does not exist', p_order_id; END IF; FOR r IN SELECT product_id, qty FROM order_items WHERE order_id = p_order_id LOOP UPDATE stock SET qty = qty - r.qty WHERE product_id = r.product_id AND qty >= r.qty; IF NOT FOUND THEN RAISE EXCEPTION 'insufficient stock for product %', r.product_id; END IF; END LOOP; INSERT INTO event_log (msg) VALUES ('order ' || p_order_id || ' processed'); COMMIT; END; \$\$;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE OR REPLACE PROCEDURE process_order(p_order_id int) LANGUAGE plpgsql AS \$\$ DECLARE r record; BEGIN IF NOT EXISTS (SELECT 1 FROM order_items WHERE order_id = p_order_id) THEN RAISE EXCEPTION 'order % does not exist', p_order_id; END IF; FOR r IN SELECT product_id, qty FROM order_items WHERE order_id = p_order_id LOOP UPDATE stock SET qty = qty - r.qty WHERE product_id = r.product_id AND qty >= r.qty; IF NOT FOUND THEN RAISE EXCEPTION 'insufficient stock for product %', r.product_id; END IF; END LOOP; INSERT INTO event_log (msg) VALUES ('order ' || p_order_id || ' processed'); COMMIT; END; \$\$;" SET CREATE PROCEDURE

✅ Process a Valid Order

Call it with an order that has enough stock for every item, and confirm every change actually persisted.

psql -U postgres -d beer_db -c "CALL process_order(1);"

psql -U postgres -d beer_db -c "SELECT * FROM stock ORDER BY product_id;"

psql -U postgres -d beer_db -c "SELECT msg FROM event_log;"

student@lab:~$ psql -U postgres -d beer_db -c "CALL process_order(1);" SET CALL student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM stock ORDER BY product_id;" SET product_id | qty ------------+----- 1 | 7 2 | 0 3 | 3 (3 rows) student@lab:~$ psql -U postgres -d beer_db -c "SELECT msg FROM event_log;" SET msg -------------------- order 1 processed (1 row)

❌ Process an Order That Cannot Be Fulfilled

Call it with an order needing more stock than exists, and prove that nothing at all was changed.

psql -U postgres -d beer_db -c "CALL process_order(2);"

psql -U postgres -d beer_db -c "SELECT * FROM stock ORDER BY product_id;"

student@lab:~$ psql -U postgres -d beer_db -c "CALL process_order(2);" SET ERROR: insufficient stock for product 2 CONTEXT: PL/pgSQL function process_order(integer) line 1 at RAISE student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM stock ORDER BY product_id;" SET product_id | qty ------------+----- 1 | 7 2 | 0 3 | 3 (3 rows)

🔍 Process an Order That Does Not Exist

Confirm the procedure validates its input before touching anything.

psql -U postgres -d beer_db -c "CALL process_order(99);"

student@lab:~$ psql -U postgres -d beer_db -c "CALL process_order(99);" SET ERROR: order 99 does not exist CONTEXT: PL/pgSQL function process_order(integer) line 1 at RAISE

Lab 2.7.6 complete — Block 2.7 complete — Certificate 2 complete. Application logic moved into the database itself, with its transactional guarantee proven, not assumed:\n\n\n Function + EXCEPTION WHEN : ✅ error caught locally, contained\n Real PROCEDURE, CALL : ✅ COMMIT inside its own body\n Valid order : ✅ every change verified persisted\n Failed order : ✅ zero partial changes, proven directly\n Invalid input : ✅ caught before anything touched\n

Enable JavaScript to run the live terminal and track your progress.