🏠 Home / Hub

🐘 PostgreSQL Lesson 07 — Stored Procedures & Functions

← Back to PostgreSQL Menu  |  🏠 Hub

1. PL/pgSQL — PostgreSQL's Procedural Language

PL/pgSQL = SQL + procedural logic (variables, loops, conditions, exceptions)
Function vs Procedure: Function returns value, Procedure does not (or uses OUT params)
Run DB logic inside DB — reduces round trips, ensures consistency 
-- Anonymous block (one-time execution)
DO $$
DECLARE
  v_count INTEGER;
  v_msg   TEXT;
BEGIN
  SELECT COUNT(*) INTO v_count FROM users;
  v_msg := 'Total users: ' || v_count;
  RAISE NOTICE '%', v_msg;    -- prints to console
END;
$$;

-- Variables
DO $$
DECLARE
  x INTEGER := 10;
  y INTEGER := 20;
  z TEXT;
BEGIN
  z := x + y;
  RAISE NOTICE 'Sum: %', z;

  -- Conditionals
  IF x > y THEN
    RAISE NOTICE 'x is bigger';
  ELSIF x = y THEN
    RAISE NOTICE 'Equal';
  ELSE
    RAISE NOTICE 'y is bigger';
  END IF;
END;
$$;

2. CREATE FUNCTION

-- Simple function
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

SELECT add_numbers(10, 20);  -- 30

-- Function with table query
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id INTEGER)
RETURNS TABLE(
  order_id   INTEGER,
  total      DECIMAL,
  status     TEXT,
  created_at TIMESTAMPTZ
) AS $$
BEGIN
  RETURN QUERY
    SELECT o.id, o.total, o.status, o.created_at
    FROM   orders o
    WHERE  o.user_id = p_user_id
    ORDER  BY o.created_at DESC;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_user_orders(1);

-- Function with conditional logic
CREATE OR REPLACE FUNCTION calculate_discount(
  p_amount    DECIMAL,
  p_user_type TEXT
)
RETURNS DECIMAL AS $$
DECLARE
  v_discount DECIMAL := 0;
BEGIN
  CASE p_user_type
    WHEN 'vip'      THEN v_discount := 0.20;
    WHEN 'premium'  THEN v_discount := 0.10;
    WHEN 'regular'  THEN v_discount := 0.05;
    ELSE                 v_discount := 0;
  END CASE;

  RETURN p_amount * (1 - v_discount);
END;
$$ LANGUAGE plpgsql;

SELECT calculate_discount(100.00, 'vip');     -- 80.00
SELECT calculate_discount(100.00, 'regular'); -- 95.00

3. Loops & Complex Logic

-- FOR loop
CREATE OR REPLACE FUNCTION generate_month_report(p_year INTEGER)
RETURNS TABLE(month_name TEXT, order_count BIGINT, total_revenue DECIMAL) AS $$
DECLARE
  v_month INTEGER;
BEGIN
  FOR v_month IN 1..12 LOOP
    RETURN QUERY
      SELECT
        TO_CHAR(MAKE_DATE(p_year, v_month, 1), 'Month') AS month_name,
        COUNT(*)              AS order_count,
        COALESCE(SUM(total), 0) AS total_revenue
      FROM orders
      WHERE EXTRACT(YEAR  FROM created_at) = p_year
        AND EXTRACT(MONTH FROM created_at) = v_month;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM generate_month_report(2024);

-- LOOP with EXIT
CREATE OR REPLACE FUNCTION find_fibonacci(p_limit INTEGER)
RETURNS INTEGER[] AS $$
DECLARE
  a INTEGER := 0;
  b INTEGER := 1;
  tmp INTEGER;
  result INTEGER[] := ARRAY[0, 1];
BEGIN
  LOOP
    tmp := a + b;
    EXIT WHEN tmp > p_limit;
    result := result || tmp;
    a := b;
    b := tmp;
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT find_fibonacci(100);  -- {0,1,1,2,3,5,8,13,21,34,55,89}

4. Exception Handling in Functions

CREATE OR REPLACE FUNCTION safe_divide(a DECIMAL, b DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
  IF b = 0 THEN
    RAISE EXCEPTION 'Division by zero! Divisor cannot be 0'
      USING ERRCODE = 'division_by_zero';
  END IF;
  RETURN a / b;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Caught division by zero';
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Unexpected error: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

-- Bank transfer with exception handling
CREATE OR REPLACE FUNCTION transfer_funds(
  p_from_id  INTEGER,
  p_to_id    INTEGER,
  p_amount   DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
  v_from_balance DECIMAL;
BEGIN
  -- Lock rows in consistent order to prevent deadlock
  SELECT balance INTO v_from_balance
  FROM   accounts
  WHERE  id = p_from_id
  FOR UPDATE;

  IF v_from_balance IS NULL THEN
    RAISE EXCEPTION 'Source account % not found', p_from_id;
  END IF;

  IF v_from_balance < p_amount THEN
    RAISE EXCEPTION 'Insufficient funds: balance=%, amount=%',
      v_from_balance, p_amount;
  END IF;

  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;

  INSERT INTO transactions(from_id, to_id, amount, created_at)
  VALUES (p_from_id, p_to_id, p_amount, NOW());

  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    -- Auto rollback on exception
    RAISE;  -- re-raise to caller
END;
$$ LANGUAGE plpgsql;

SELECT transfer_funds(1, 2, 500.00);

5. CREATE PROCEDURE (PostgreSQL 11+)

-- PROCEDURE vs FUNCTION:
-- Procedure: no RETURN, can use COMMIT/ROLLBACK inside

CREATE OR REPLACE PROCEDURE archive_old_orders(p_days INTEGER)
LANGUAGE plpgsql AS $$
DECLARE
  v_cutoff   TIMESTAMPTZ := NOW() - (p_days || ' days')::INTERVAL;
  v_count    INTEGER;
BEGIN
  -- Move old orders to archive table
  WITH moved AS (
    DELETE FROM orders
    WHERE  created_at < v_cutoff
      AND  status = 'delivered'
    RETURNING *
  )
  INSERT INTO orders_archive SELECT * FROM moved;

  GET DIAGNOSTICS v_count = ROW_COUNT;
  RAISE NOTICE 'Archived % orders older than % days', v_count, p_days;
  COMMIT;  -- Procedures can commit!
END;
$$;

-- Call procedure
CALL archive_old_orders(90);

-- Manage functions
\df                           -- list all functions
\df get_user_orders           -- specific function
DROP FUNCTION get_user_orders(INTEGER);
DROP PROCEDURE archive_old_orders(INTEGER);
💡 Functions are great for: business rules, complex calculations, data validation, reusable query logic

← PG 06  |  Next: PG 08 → Triggers →

📌 Study Checklist