Lesson 26: Stored Procedures & Functions

Stored procedures/functions package reusable SQL logic inside the database itself — useful for logic that must run atomically close to the data.

Real-time example

A transfer_funds function debits and credits two accounts in one atomic call instead of the application sending two separate statements.

CREATE OR REPLACE FUNCTION transfer_funds(from_id INT, to_id INT, amt NUMERIC)
RETURNS VOID AS $$
BEGIN
    UPDATE accounts SET balance = balance - amt WHERE id = from_id;
    UPDATE accounts SET balance = balance + amt WHERE id = to_id;
END;
$$ LANGUAGE plpgsql;

SELECT transfer_funds(1, 2, 500);

What's happening

The two UPDATEs inside the function run as one atomic unit — either both happen or neither does, without the application coordinating a transaction itself.

Use stored procedures sparingly for genuinely atomic, data-adjacent logic — business logic in application code is usually easier to test and version.