Control Flow and Error Handling

Learning Focus

Use this lesson to add control flow (branching and loops) and error handling (EXCEPTION) to PostgreSQL PL/pgSQL functions/procedures.

Concept Overview

PL/pgSQL is PostgreSQL's procedural language for writing functions and procedures with:

• variables
• branching (IF, CASE)
• loops (LOOP, WHILE, FOR)
• exceptions (BEGIN ... EXCEPTION ... END)
• messages (RAISE NOTICE, RAISE EXCEPTION)

Why is it important?

• Safety: fail fast with meaningful errors
• Robustness: handle expected error cases gracefully
• Maintainability: encode workflow logic clearly
• Correctness: enforce invariants close to the data

Where does it fit?

Control flow and exceptions appear in:

• batch routines
• data cleanup/backfill scripts
• triggers and audit logic
• validation helpers

---

Syntax & Rules

Core Syntax

IF/ELSIF/ELSE:

IF condition THEN
  ...
ELSIF other_condition THEN
  ...
ELSE
  ...
END IF;

CASE:

CASE
  WHEN cond THEN ...
  WHEN cond2 THEN ...
  ELSE ...
END CASE;

Loops:

LOOP
  EXIT WHEN done;
END LOOP;

FOR i IN 1..10 LOOP
  ...
END LOOP;

Exceptions:

BEGIN
  -- risky statement
EXCEPTION
  WHEN unique_violation THEN
    -- handle
END;

RAISE:

RAISE NOTICE 'message: %', value;
RAISE EXCEPTION 'bad input: %', value;

Available Options / Parameters

Tool	Example	Notes
RAISE NOTICE	RAISE NOTICE 'x';	logs a message (useful for debugging)
RAISE EXCEPTION	RAISE EXCEPTION 'x';	aborts the statement (and usually the transaction)
Exception names	unique_violation, foreign_key_violation	match common SQLSTATE categories
GET STACKED DIAGNOSTICS	advanced	inspect error details inside handlers

Key Rules and Considerations

• Exceptions roll back the current statement; inside an EXCEPTION block you can continue logic.
• Use exceptions for truly exceptional conditions, not normal control flow.
• Prefer constraints for data integrity, and use procedural code to orchestrate complex workflows.

---

Step-by-Step Examples

Example 1: Branching With IF (Beginner)

CREATE OR REPLACE FUNCTION classify_amount(p_amount numeric)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF p_amount < 0 THEN
    RAISE EXCEPTION 'amount must be non-negative: %', p_amount;
  ELSIF p_amount = 0 THEN
    RETURN 'zero';
  ELSIF p_amount < 10 THEN
    RETURN 'small';
  ELSE
    RETURN 'large';
  END IF;
END;
$$;

SELECT classify_amount(5) AS c;

Expected output:

    c
---------
 small
(1 row)

Explanation:

• Use RAISE EXCEPTION to enforce input rules.

---

Example 2: Looping to Process Batches (Intermediate)

This example shows a pattern for batch processing ids.

DROP TABLE IF EXISTS work_items;

CREATE TABLE work_items (
  item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL DEFAULT 'pending'
);

INSERT INTO work_items (status) VALUES
  ('pending'), ('pending'), ('pending');

CREATE OR REPLACE PROCEDURE process_pending_items(IN p_limit integer)
LANGUAGE plpgsql
AS $$
DECLARE
  v_count integer := 0;
  v_id bigint;
BEGIN
  FOR v_id IN
    SELECT item_id
    FROM work_items
    WHERE status = 'pending'
    ORDER BY item_id
    LIMIT p_limit
  LOOP
    UPDATE work_items SET status = 'done' WHERE item_id = v_id;
    v_count := v_count + 1;
  END LOOP;

  RAISE NOTICE 'processed % items', v_count;
END;
$$;

CALL process_pending_items(2);

SELECT item_id, status FROM work_items ORDER BY item_id;

Expected output (NOTICE output may vary by client):

DROP TABLE
CREATE TABLE
INSERT 0 3
CREATE PROCEDURE
CALL
 item_id | status
---------+--------
 1       | done
 2       | done
 3       | pending
(3 rows)

Explanation:

• FOR v_id IN SELECT ... LOOP is a common batch-processing pattern.
• RAISE NOTICE is helpful during development.

---

Example 3: Handle Unique Violations With EXCEPTION (Advanced)

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

CREATE OR REPLACE FUNCTION create_user_or_return_existing(p_email text)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  v_id bigint;
BEGIN
  BEGIN
    INSERT INTO users (email)
    VALUES (p_email)
    RETURNING user_id INTO v_id;
  EXCEPTION
    WHEN unique_violation THEN
      SELECT user_id INTO v_id
      FROM users
      WHERE email = p_email;
  END;

  RETURN v_id;
END;
$$;

SELECT create_user_or_return_existing('a@example.com') AS id1;
SELECT create_user_or_return_existing('a@example.com') AS id2;

Expected output:

 id1
-----
 1
(1 row)

 id2
-----
 1
(1 row)

Explanation:

• The first call inserts.
• The second call hits the unique constraint and returns the existing id.

---

Practical Use Cases

1) Validate Inputs Before Writing

Context: prevent invalid operations.

IF p_amount < 0 THEN
  RAISE EXCEPTION 'negative amount not allowed';
END IF;

2) Batch Updates With a Limit

Context: avoid long locks by processing a limited batch.

CALL process_pending_items(1000);

3) Friendly Error Messages

Context: raise meaningful messages when constraints fail.

RAISE EXCEPTION 'user % does not exist', p_user_id;

---

Common Mistakes & Troubleshooting

Mistake 1: Using Exceptions as Normal Control Flow

What happens: code becomes slower and harder to read.

Fix: use normal branching when possible; reserve EXCEPTION for true errors.

---

Mistake 2: Swallowing Errors Without Logging

What happens: silent failures.

Fix: RAISE NOTICE or re-raise exceptions when you cannot recover.

---

Mistake 3: Re-implementing Constraints in Code

What happens: drift between procedural rules and schema rules.

Fix: enforce invariants with constraints; use PL/pgSQL for orchestration.

Debugging tips:

1. Use RAISE NOTICE during development, then remove or reduce noise.
2. Keep exception handlers narrow (catch only what you can handle).
3. Test edge cases: NULL inputs, duplicates, FK violations.
4. Use \df+ and \sf in psql to inspect routines.

---

Best Practices

• ✅ Validate inputs early; ❌ let invalid values run deep into the procedure.
• ✅ Catch only exceptions you can handle; ❌ catch-all and ignore errors.
• ✅ Prefer constraints for integrity; ❌ duplicate integrity logic in code.
• ✅ Batch large operations; ❌ run massive updates in one long transaction without control.
• ✅ Keep PL/pgSQL readable; ❌ write deeply nested, untestable blocks.

---

Hands-On Practice

Exercise 1 (Easy): Write an IF-based function

Task: Create a function practice_is_positive(int) that returns true if > 0 else false.

-- Your SQL here

Solution:

CREATE OR REPLACE FUNCTION practice_is_positive(p int)
RETURNS boolean
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF p > 0 THEN
    RETURN true;
  ELSE
    RETURN false;
  END IF;
END;
$$;

---

Exercise 2 (Medium): Add a loop

Task: Create a function practice_sum_1_to_n(int) that sums 1..n.

-- Your SQL here

Solution:

CREATE OR REPLACE FUNCTION practice_sum_1_to_n(n int)
RETURNS int
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
  s int := 0;
  i int;
BEGIN
  FOR i IN 1..n LOOP
    s := s + i;
  END LOOP;
  RETURN s;
END;
$$;

---

Exercise 3 (Advanced): Catch a unique violation

Task: Write a function that inserts into a table with a UNIQUE column and returns existing id on duplicates.

-- Your SQL here

Solution:

CREATE TABLE IF NOT EXISTS practice_unique (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  k text NOT NULL UNIQUE
);

CREATE OR REPLACE FUNCTION practice_upsert_like(p_k text)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  v_id bigint;
BEGIN
  BEGIN
    INSERT INTO practice_unique (k)
    VALUES (p_k)
    RETURNING id INTO v_id;
  EXCEPTION
    WHEN unique_violation THEN
      SELECT id INTO v_id FROM practice_unique WHERE k = p_k;
  END;

  RETURN v_id;
END;
$$;

---

Connection to Other Concepts

Concept	Why it matters
Procedures	procedures often contain loops and orchestration
Functions	functions may validate inputs and raise errors
Constraints	exceptions frequently originate from constraint violations
Transactions	exceptions influence rollback behavior
Triggers	trigger functions often use control flow and exceptions

---

Visual Learning Diagram

flowchart TD
  A[Start] --> B{IF/CASE}
  B --> C[Do work]
  C --> D{Loop?}
  D -->|Yes| C
  D -->|No| E[BEGIN]
  E --> F[Risky statement]
  F --> G{Error?}
  G -->|No| H[RETURN]
  G -->|Yes| I[EXCEPTION handler]
  I --> H

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I allNodes
  class I highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Broad exception handlers	silent bugs	catch specific exceptions only
Missing logging	hard debugging	use RAISE NOTICE during development
Using exceptions for flow	slow and unclear	use IF/CASE instead
Replacing constraints	drift	enforce invariants in schema
Huge loops without limits	long locks	process in batches

---

Quick Reference

IF cond THEN ... END IF;
FOR x IN SELECT ... LOOP ... END LOOP;
RAISE NOTICE 'msg: %', v;
BEGIN ... EXCEPTION WHEN unique_violation THEN ... END;
RAISE EXCEPTION 'error'

What's Next

• Continue to 14. Triggers - Automate logic on INSERT/UPDATE/DELETE.
• Module Overview - Return to Stored Procedures and Functions overview.