Skip to content

Stored Procedures and Functions

Medium ~40 min

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. 1

    Set up banking tables

    Create accounts and transfers tables for a simple banking system.

    Code
    CREATE 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. 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.

    Code
    CREATE 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. 3

    Call the procedure

    Call the transfer_funds procedure to move 200 from Alice to Bob, then verify the balances.

  4. 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.

  5. 5

    Add overdraft protection

    Modify the transfer procedure to check for sufficient funds before transferring. Raise an exception if the balance is too low.

    Code
    CREATE 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

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

Related Glossary

Related Glossary

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to topic