Day 11: Functions, Procedures, and Control Flow

Welcome to Day 11! 🧠

Today you’ll learn how to encapsulate logic inside PostgreSQL using SQL and PL/pgSQL functions, stored procedures, and control flow constructs.

---

What You'll Learn

1. SQL vs PL/pgSQL functions
2. Creating functions with parameters and returns
3. Control flow: IF, CASE, loops
4. Stored procedures (CALL) vs functions (SELECT)
5. Error handling with EXCEPTION
6. Best practices and security (VOLATILE/STABLE/IMMUTABLE)

---

1) Creating SQL and PL/pgSQL Functions

-- Simple SQL function
CREATE OR REPLACE FUNCTION add_tax(amount numeric)
RETURNS numeric
LANGUAGE sql
AS $$
  SELECT amount * 1.18;
$$;

-- PL/pgSQL function with variables
CREATE OR REPLACE FUNCTION pct_diff(a numeric, b numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
  diff numeric;
BEGIN
  IF b = 0 THEN
    RAISE EXCEPTION 'Division by zero';
  END IF;
  diff := (a - b) * 100.0 / b;
  RETURN diff;
END;
$$;

---

2) Parameters, Defaults, Named args

CREATE OR REPLACE FUNCTION greet(name text DEFAULT 'friend')
RETURNS text AS $$ SELECT 'Hello, ' || name $$ LANGUAGE sql;

-- Named parameter call
SELECT greet(name := 'Hardik');

---

3) Control Flow in PL/pgSQL

CREATE OR REPLACE FUNCTION bucket_salary(s numeric)
RETURNS text LANGUAGE plpgsql AS $$
BEGIN
  IF s < 50000 THEN RETURN 'low';
  ELSIF s < 80000 THEN RETURN 'mid';
  ELSE RETURN 'high';
  END IF;
END; $$;

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

---

4) Procedures vs Functions

• Functions return values and can be used in SELECT
• Procedures do not return values; invoked with CALL and can manage transactions

-- Procedure example
CREATE OR REPLACE PROCEDURE archive_old_orders(days_old int)
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO orders_archive SELECT * FROM orders
  WHERE created_at < now() - (days_old || ' days')::interval;
  DELETE FROM orders
  WHERE created_at < now() - (days_old || ' days')::interval;
END; $$;

CALL archive_old_orders(365);

---

5) Error Handling and EXCEPTION

CREATE OR REPLACE FUNCTION safe_div(a numeric, b numeric)
RETURNS numeric LANGUAGE plpgsql AS $$
DECLARE res numeric;
BEGIN
  BEGIN
    res := a / b;
  EXCEPTION WHEN division_by_zero THEN
    res := NULL; -- or RETURN 0
  END;
  RETURN res;
END; $$;

---

6) Volatility Categories

• IMMUTABLE: same input -> same output; no table access
• STABLE: can read tables but not modify; same within a statement
• VOLATILE: can change anytime; default for PL/pgSQL

CREATE OR REPLACE FUNCTION today()
RETURNS date LANGUAGE sql STABLE AS $$ SELECT CURRENT_DATE $$;

---

7) Security and Permissions

• SECURITY DEFINER to run with function owner privileges (use cautiously)
• Grant EXECUTE on functions to roles as needed

ALTER FUNCTION archive_old_orders(int) OWNER TO dba;
GRANT EXECUTE ON FUNCTION archive_old_orders(int) TO reporting_role;

---

Real-World Example: Loyalty Tier Assignment

CREATE OR REPLACE FUNCTION assign_tier(total_spent numeric)
RETURNS text LANGUAGE plpgsql AS $$
BEGIN
  CASE 
    WHEN total_spent >= 5000 THEN RETURN 'Gold';
    WHEN total_spent >= 2000 THEN RETURN 'Silver';
    ELSE RETURN 'Bronze';
  END CASE;
END; $$;

-- Use in query
SELECT customer_id, assign_tier(SUM(total_amount))
FROM orders
GROUP BY customer_id;

---

Today's Challenge 🎯

1) Write a function normalize_email(text) that trims and lowercases emails
2) Write a safe_transfer(from_acct, to_acct, amount) procedure that debits and credits with basic checks
3) Add EXCEPTION handling to log failures into an audit table

-- Your solutions here

---

Summary

• Functions encapsulate reusable logic; procedures orchestrate side effects
• Control flow and exceptions enable robust data operations

Coming Up Next

Day 12: Triggers and Events — automate reactions to data changes.

PostgreSQL #SQL #PLpgSQL #Functions #Procedures #15DaysOfPostgreSQL