Stored Procedures and Functions
What you'll build
A set of stored procedures for a banking app — transfer funds with a transaction, compute account balances, and log transfers.
Goals
- Write stored procedures with parameters
- Use transaction control inside procedures
- Create user-defined functions
- Handle errors with RAISE EXCEPTION
Prerequisites
- INSERT, UPDATE, DELETE
- Transaction basics (BEGIN/COMMIT)
- Functions and procedures concept
Steps
- 1
Set up banking tables
Create accounts and transfers tables for a simple banking system.
CodeCREATE TABLE accounts ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, balance NUMERIC(12,2) NOT NULL DEFAULT 0 ); CREATE TABLE transfers ( id SERIAL PRIMARY KEY, from_acct INTEGER REFERENCES accounts(id), to_acct INTEGER REFERENCES accounts(id), amount NUMERIC(12,2) NOT NULL, created_at TIMESTAMP DEFAULT NOW() ); INSERT INTO accounts (name, balance) VALUES ('Alice', 1000.00), ('Bob', 500.00);Expected result
Two accounts created: Alice with 1000 and Bob with 500.
- 2
Write a transfer procedure
Create a stored procedure that transfers money between accounts atomically — debit one, credit the other, log the transfer, all in a transaction.
CodeCREATE OR REPLACE PROCEDURE transfer_funds( p_from INTEGER, p_to INTEGER, p_amount NUMERIC(12,2) ) LANGUAGE plpgsql AS $$ BEGIN IF p_amount <= 0 THEN RAISE EXCEPTION 'Amount must be positive'; END IF; UPDATE accounts SET balance = balance - p_amount WHERE id = p_from; UPDATE accounts SET balance = balance + p_amount WHERE id = p_to; INSERT INTO transfers (from_acct, to_acct, amount) VALUES (p_from, p_to, p_amount); COMMIT; END; $$;Expected result
The procedure is created and ready to transfer funds atomically.
- 3
Call the procedure
Call the transfer_funds procedure to move 200 from Alice to Bob, then verify the balances.
Hint
Use CALL transfer_funds(1, 2, 200.00); then SELECT * FROM accounts;
- 4
Create a balance function
Write a user-defined function that returns the current balance for a given account ID. Functions can be used in SELECT statements.
Hint
CREATE FUNCTION get_balance(p_id INTEGER) RETURNS NUMERIC AS $$ ... $$ LANGUAGE sql;
- 5
Add overdraft protection
Modify the transfer procedure to check for sufficient funds before transferring. Raise an exception if the balance is too low.
CodeCREATE OR REPLACE PROCEDURE transfer_funds( p_from INTEGER, p_to INTEGER, p_amount NUMERIC(12,2) ) LANGUAGE plpgsql AS $$ DECLARE v_balance NUMERIC(12,2); BEGIN SELECT balance INTO v_balance FROM accounts WHERE id = p_from; IF v_balance < p_amount THEN RAISE EXCEPTION 'Insufficient funds: have %, need %', v_balance, p_amount; END IF; UPDATE accounts SET balance = balance - p_amount WHERE id = p_from; UPDATE accounts SET balance = balance + p_amount WHERE id = p_to; INSERT INTO transfers (from_acct, to_acct, amount) VALUES (p_from, p_to, p_amount); END; $$;Expected result
Transfers that exceed the balance now raise an exception instead of going negative.
Try it yourself
CREATE OR REPLACE PROCEDURE transfer_funds(p_from INTEGER, p_to INTEGER, p_amount NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
-- TODO: implement transfer
END;
$$;CREATE OR REPLACE PROCEDURE transfer_funds(p_from INTEGER, p_to INTEGER, p_amount NUMERIC(12,2))
LANGUAGE plpgsql AS $$
BEGIN
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
INSERT INTO transfers (from_acct, to_acct, amount) VALUES (p_from, p_to, p_amount);
END;
$$;What you learned
You wrote stored procedures and functions — encapsulating business logic in the database with transaction safety, parameter validation, and error handling.
Related Resources
Related Lessons
- Transactions
ACID transactions.
Related Glossary
- Stored Procedure
Precompiled SQL logic.
- Transaction
Atomic operations.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
- Stored Procedure
Precompiled SQL logic.
- Transaction
Atomic operations.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.