User-Defined Functions

// Part 01

Why Write Your Own Functions

CREATE OR REPLACE FUNCTION function_name(
  param1  data_type,
  param2  data_type DEFAULT default_value
)
RETURNS return_type
LANGUAGE plpgsql
IMMUTABLE   -- or STABLE or VOLATILE (covered in Part 05)
AS $$
DECLARE
  v_result  data_type;
BEGIN
  -- compute the result
  v_result := param1 + param2;
  RETURN v_result;
END;
$$;

-- Call it anywhere in SQL:
SELECT function_name(col1, col2) FROM table;
WHERE function_name(col) > threshold
ORDER BY function_name(col) DESC

// Part 02

SQL Functions vs PL/pgSQL Functions

-- SQL function: single expression, no procedural logic
-- The planner can inline this directly into the calling query
CREATE OR REPLACE FUNCTION fn_gross_profit(
  p_unit_price  NUMERIC,
  p_cost_price  NUMERIC
) RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE STRICT    -- STRICT: returns NULL if any input is NULL
AS $$
  SELECT ROUND(p_unit_price - p_cost_price, 2)
$$;

-- PL/pgSQL function: requires conditionals, variables, or multiple statements
CREATE OR REPLACE FUNCTION fn_price_band(p_price NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  RETURN CASE
    WHEN p_price < 50   THEN 'Budget'
    WHEN p_price < 150  THEN 'Standard'
    WHEN p_price < 300  THEN 'Premium'
    ELSE 'Luxury'
  END;
END;
$$;

-- When to use SQL:
-- ✅ Single expression (arithmetic, string op, date calc)
-- ✅ No variables or control flow needed
-- ✅ Performance-critical — planner can inline it
-- ✅ Works across many databases (standard SQL body)

-- When to use PL/pgSQL:
-- ✅ Conditional logic (IF/CASE with side effects)
-- ✅ Variables needed across multiple steps
-- ✅ Exception handling (EXCEPTION block)
-- ✅ Calling other procedures/functions in sequence

// Part 03

Scalar Functions — The Most Common Type

-- Gross margin percentage
CREATE OR REPLACE FUNCTION fn_margin_pct(
  p_price  NUMERIC,
  p_cost   NUMERIC
) RETURNS NUMERIC
LANGUAGE sql IMMUTABLE STRICT AS $$
  SELECT ROUND((p_price - p_cost) / NULLIF(p_price, 0) * 100, 1)
$$;

-- Customer lifetime value tier
CREATE OR REPLACE FUNCTION fn_ltv_tier(p_lifetime_value NUMERIC)
RETURNS TEXT
LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE
    WHEN p_lifetime_value >= 5000 THEN 'Platinum'
    WHEN p_lifetime_value >= 2000 THEN 'Gold'
    WHEN p_lifetime_value >= 500  THEN 'Silver'
    ELSE 'Bronze'
  END
$$;

-- Delivery performance label
CREATE OR REPLACE FUNCTION fn_delivery_label(p_days INTEGER)
RETURNS TEXT
LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE
    WHEN p_days IS NULL THEN 'Not delivered'
    WHEN p_days <= 1    THEN 'Same/next day'
    WHEN p_days <= 3    THEN 'Standard'
    WHEN p_days <= 7    THEN 'Slow'
    ELSE 'Very slow'
  END
$$;

-- Display-formatted rupee amount
CREATE OR REPLACE FUNCTION fn_format_inr(p_amount NUMERIC)
RETURNS TEXT
LANGUAGE sql IMMUTABLE AS $$
  SELECT '₹' || TO_CHAR(p_amount, 'FM999,999,990.00')
$$;

-- Safe percentage (handles zero denominator)
CREATE OR REPLACE FUNCTION fn_pct(p_part NUMERIC, p_total NUMERIC)
RETURNS NUMERIC
LANGUAGE sql IMMUTABLE AS $$
  SELECT ROUND(p_part / NULLIF(p_total, 0) * 100, 1)
$$;

-- Valid Indian mobile number: 10 digits starting with 6-9
CREATE OR REPLACE FUNCTION fn_is_valid_mobile(p_phone TEXT)
RETURNS BOOLEAN
LANGUAGE sql IMMUTABLE AS $$
  SELECT REGEXP_REPLACE(COALESCE(p_phone, ''), '[^0-9]', '', 'g') ~ '^[6-9][0-9]{9}$'
$$;

-- Valid email: has exactly one @ and at least one dot in domain
CREATE OR REPLACE FUNCTION fn_is_valid_email(p_email TEXT)
RETURNS BOOLEAN
LANGUAGE sql IMMUTABLE AS $$
  SELECT COALESCE(p_email, '') ~ '^[^@]+@[^@]+.[^@]+$'
$$;

-- Valid zip_code: exactly 6 digits
CREATE OR REPLACE FUNCTION fn_is_valid_zip_code(p_pin TEXT)
RETURNS BOOLEAN
LANGUAGE sql IMMUTABLE AS $$
  SELECT COALESCE(p_pin, '') ~ '^[0-9]{6}$'
$$;

-- Usage in a data quality audit:
SELECT
  customer_id,
  email,
  fn_is_valid_email(email)    AS email_valid,
  phone,
  fn_is_valid_mobile(phone)   AS phone_valid
FROM customers
WHERE NOT fn_is_valid_email(email)
   OR NOT fn_is_valid_mobile(phone);

// Part 04

Table-Valued Functions — Returning Result Sets

-- RETURNS TABLE: define the output columns explicitly
CREATE OR REPLACE FUNCTION fn_store_daily_report(
  p_store_id  TEXT,
  p_from      DATE DEFAULT '2024-01-01',
  p_to        DATE DEFAULT CURRENT_DATE
)
RETURNS TABLE (
  order_date      DATE,
  order_count     BIGINT,
  daily_revenue   NUMERIC,
  avg_order       NUMERIC,
  top_category    TEXT
)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
  RETURN QUERY
  WITH daily AS (
    SELECT
      o.order_date,
      COUNT(DISTINCT o.order_id)          AS cnt,
      ROUND(SUM(o.total_amount), 2)       AS rev,
      ROUND(AVG(o.total_amount), 2)       AS avg_val,
      p.category,
      ROUND(SUM(oi.line_total), 2)        AS cat_rev,
      RANK() OVER (
        PARTITION BY o.order_date
        ORDER BY SUM(oi.line_total) DESC
      )                                   AS cat_rank
    FROM orders      AS o
    JOIN order_items AS oi ON o.order_id    = oi.order_id
    JOIN products    AS p  ON oi.product_id = p.product_id
    WHERE o.store_id     = p_store_id
      AND o.order_status = 'Delivered'
      AND o.order_date BETWEEN p_from AND p_to
    GROUP BY o.order_date, p.category
  )
  SELECT
    order_date,
    cnt,
    rev,
    avg_val,
    category
  FROM daily
  WHERE cat_rank = 1
  ORDER BY order_date;
END;
$$;

-- Use it exactly like a table:
SELECT * FROM fn_store_daily_report('ST001');
SELECT * FROM fn_store_daily_report('ST001', '2024-01-01', '2024-01-31');

-- JOIN the function result to other tables:
SELECT s.city, r.daily_revenue, r.top_category
FROM fn_store_daily_report('ST001') AS r
CROSS JOIN stores AS s
WHERE s.store_id = 'ST001';

-- Simple SQL TVF: parameterised view for top-N products by category
CREATE OR REPLACE FUNCTION fn_top_products_by_category(
  p_category  TEXT,
  p_limit     INTEGER DEFAULT 5
)
RETURNS TABLE (
  product_id    INTEGER,
  product_name  TEXT,
  unit_price    NUMERIC,
  margin_pct    NUMERIC,
  times_ordered BIGINT
)
LANGUAGE sql STABLE
AS $$
  SELECT
    p.product_id,
    p.product_name,
    p.unit_price,
    ROUND((p.unit_price - p.cost_price) / NULLIF(p.unit_price, 0) * 100, 1),
    COUNT(DISTINCT oi.order_id)
  FROM products    AS p
  LEFT JOIN order_items AS oi ON p.product_id = oi.product_id
  WHERE p.category = p_category
  GROUP BY p.product_id, p.product_name, p.unit_price, p.cost_price
  ORDER BY COUNT(DISTINCT oi.order_id) DESC
  LIMIT p_limit
$$;

-- Call:
SELECT * FROM fn_top_products_by_category('Dairy');
SELECT * FROM fn_top_products_by_category('Bakery', 3);

// Part 05

IMMUTABLE, STABLE, VOLATILE — Volatility Categories

-- IMMUTABLE: safe in indexes, cached aggressively
CREATE INDEX idx_products_margin ON products (
  fn_margin_pct(unit_price, cost_price)
);
-- The planner knows fn_margin_pct is IMMUTABLE:
-- WHERE fn_margin_pct(unit_price, cost_price) > 40
-- → uses the functional index directly (fast)

-- STABLE: cannot be in indexes, but planner evaluates once per query
CREATE OR REPLACE FUNCTION fn_current_month_revenue(p_store_id TEXT)
RETURNS NUMERIC
LANGUAGE plpgsql STABLE AS $$
DECLARE v_rev NUMERIC;
BEGIN
  SELECT ROUND(SUM(total_amount), 2) INTO v_rev
  FROM orders
  WHERE store_id = p_store_id
    AND order_status = 'Delivered'
    AND DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE);
  RETURN COALESCE(v_rev, 0);
END;
$$;

-- VOLATILE: evaluated fresh on every row — no caching
CREATE OR REPLACE FUNCTION fn_random_discount()
RETURNS NUMERIC
LANGUAGE sql VOLATILE AS $$
  SELECT ROUND(RANDOM() * 20 + 5, 0)   -- random 5-25% discount
$$;

-- WRONG — declaring IMMUTABLE when function reads the database:
-- CREATE FUNCTION fn_customer_count() RETURNS BIGINT
-- LANGUAGE sql IMMUTABLE AS $$ SELECT COUNT(*) FROM customers $$;
-- ↑ IMMUTABLE is a LIE — the planner will cache a stale result
-- Always use STABLE for functions that read tables

// Part 06

Function Overloading — Same Name, Different Parameters

-- Overload 1: margin_pct from price and cost
CREATE OR REPLACE FUNCTION fn_margin_pct(
  p_price  NUMERIC,
  p_cost   NUMERIC
) RETURNS NUMERIC
LANGUAGE sql IMMUTABLE AS $$
  SELECT ROUND((p_price - p_cost) / NULLIF(p_price, 0) * 100, 1)
$$;

-- Overload 2: margin_pct from product_id (looks up from table)
CREATE OR REPLACE FUNCTION fn_margin_pct(
  p_product_id  INTEGER
) RETURNS NUMERIC
LANGUAGE plpgsql STABLE AS $$
DECLARE v_price NUMERIC; v_cost NUMERIC;
BEGIN
  SELECT unit_price, cost_price INTO v_price, v_cost
  FROM products WHERE product_id = p_product_id;
  RETURN ROUND((v_price - v_cost) / NULLIF(v_price, 0) * 100, 1);
END;
$$;

-- Overload 3: format_display as TEXT (one arg)
CREATE OR REPLACE FUNCTION fn_format_display(p_amount NUMERIC)
RETURNS TEXT
LANGUAGE sql IMMUTABLE AS $$
  SELECT '₹' || TO_CHAR(p_amount, 'FM999,999,990.00')
$$;

-- Overload 4: format_display with currency code (two args)
CREATE OR REPLACE FUNCTION fn_format_display(
  p_amount    NUMERIC,
  p_currency  TEXT
) RETURNS TEXT
LANGUAGE sql IMMUTABLE AS $$
  SELECT p_currency || ' ' || TO_CHAR(p_amount, 'FM999,999,990.00')
$$;

-- Caller does not need to know which overload:
SELECT fn_margin_pct(100.0, 60.0);     -- → calls Overload 1
SELECT fn_margin_pct(5);               -- → calls Overload 2
SELECT fn_format_display(1250.50);     -- → calls Overload 3
SELECT fn_format_display(1250.50,'$'); -- → calls Overload 4

// Part 07

STRICT — Automatic NULL Propagation

-- WITHOUT STRICT: must handle NULLs manually
CREATE OR REPLACE FUNCTION fn_margin_pct_manual(
  p_price  NUMERIC,
  p_cost   NUMERIC
) RETURNS NUMERIC
LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE
    WHEN p_price IS NULL OR p_cost IS NULL THEN NULL
    ELSE ROUND((p_price - p_cost) / NULLIF(p_price, 0) * 100, 1)
  END
$$;

-- WITH STRICT: NULL propagation is automatic — body is cleaner
CREATE OR REPLACE FUNCTION fn_margin_pct(
  p_price  NUMERIC,
  p_cost   NUMERIC
) RETURNS NUMERIC
LANGUAGE sql IMMUTABLE STRICT AS $$
  SELECT ROUND((p_price - p_cost) / NULLIF(p_price, 0) * 100, 1)
$$;
-- If p_price IS NULL OR p_cost IS NULL → returns NULL immediately
-- The body never executes when any argument is NULL

-- When NOT to use STRICT:
-- When NULL input should produce a non-NULL output
-- e.g. fn_coalesce_label(p_value TEXT) should return 'N/A' for NULL p_value
-- Using STRICT would make it return NULL instead — wrong behaviour

// Part 08

SECURITY DEFINER — Elevated Privilege Functions

-- Scenario: app_user has NO SELECT on the customers table
-- But needs to look up customer loyalty tier by customer_id
-- (to personalise the UI — should not see full customer data)

-- Create the function as a privileged user (admin role):
CREATE OR REPLACE FUNCTION fn_get_loyalty_tier(p_customer_id INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
STABLE
SECURITY DEFINER          -- runs as the function creator, not the caller
SET search_path = public  -- IMPORTANT: always set search_path in SECURITY DEFINER
AS $$
DECLARE
  v_tier TEXT;
BEGIN
  -- This SELECT runs with CREATOR's privileges (admin), not app_user's
  SELECT loyalty_tier INTO v_tier
  FROM customers
  WHERE customer_id = p_customer_id;
  RETURN v_tier;
END;
$$;

-- Grant EXECUTE to the app role:
GRANT EXECUTE ON FUNCTION fn_get_loyalty_tier(INTEGER) TO app_user;
-- Revoke direct table access:
REVOKE SELECT ON customers FROM app_user;

-- Now app_user can call the function but cannot query customers directly:
SELECT fn_get_loyalty_tier(42);    -- works (runs as admin)
SELECT * FROM customers;           -- ERROR: permission denied

-- SECURITY RISK: always SET search_path to prevent search_path injection
-- A malicious user could create a schema that shadows system functions
-- SET search_path = public forces the function to use the correct schema

// Part 09

Building a Reusable Function Library

-- ── NAMING CONVENTION ──────────────────────────────────────────
-- fn_<domain>_<action>
-- fn_product_margin_pct   — product domain
-- fn_customer_ltv_tier    — customer domain
-- fn_order_delivery_label — order domain
-- fn_format_inr           — formatting utilities

-- ── PRODUCT DOMAIN ──────────────────────────────────────────────
-- Gross margin percentage
CREATE OR REPLACE FUNCTION fn_product_margin_pct(
  p_price NUMERIC, p_cost NUMERIC
) RETURNS NUMERIC
LANGUAGE sql IMMUTABLE STRICT
COMMENT ON FUNCTION fn_product_margin_pct IS
  'Returns gross margin % = (price - cost) / price * 100. NULL if either input is NULL.'
AS $$ SELECT ROUND((p_price - p_cost) / NULLIF(p_price, 0) * 100, 1) $$;

-- Price band classification
CREATE OR REPLACE FUNCTION fn_product_price_band(p_price NUMERIC)
RETURNS TEXT
LANGUAGE sql IMMUTABLE
AS $$ SELECT CASE
  WHEN p_price IS NULL THEN 'Unknown'
  WHEN p_price < 50    THEN 'Budget'
  WHEN p_price < 150   THEN 'Standard'
  WHEN p_price < 300   THEN 'Premium'
  ELSE 'Luxury'
END $$;

-- ── CUSTOMER DOMAIN ──────────────────────────────────────────────
-- Lifetime value tier
CREATE OR REPLACE FUNCTION fn_customer_ltv_tier(p_ltv NUMERIC)
RETURNS TEXT
LANGUAGE sql IMMUTABLE
AS $$ SELECT CASE
  WHEN p_ltv IS NULL   THEN 'New'
  WHEN p_ltv >= 5000   THEN 'Platinum'
  WHEN p_ltv >= 2000   THEN 'Gold'
  WHEN p_ltv >= 500    THEN 'Silver'
  ELSE 'Bronze'
END $$;

-- Days since last order → recency label
CREATE OR REPLACE FUNCTION fn_customer_recency_label(p_days INTEGER)
RETURNS TEXT
LANGUAGE sql IMMUTABLE
AS $$ SELECT CASE
  WHEN p_days IS NULL THEN 'Never ordered'
  WHEN p_days <= 30   THEN 'Active'
  WHEN p_days <= 90   THEN 'At risk'
  WHEN p_days <= 180  THEN 'Lapsing'
  ELSE 'Churned'
END $$;

-- ── ORDER DOMAIN ─────────────────────────────────────────────────
-- Delivery speed label
CREATE OR REPLACE FUNCTION fn_order_delivery_label(p_days INTEGER)
RETURNS TEXT
LANGUAGE sql IMMUTABLE
AS $$ SELECT CASE
  WHEN p_days IS NULL THEN 'Pending'
  WHEN p_days <= 1    THEN 'Express'
  WHEN p_days <= 3    THEN 'Standard'
  WHEN p_days <= 7    THEN 'Slow'
  ELSE 'Very slow'
END $$;

-- ── FORMATTING UTILITIES ─────────────────────────────────────────
-- INR display format
CREATE OR REPLACE FUNCTION fn_format_inr(p_amount NUMERIC)
RETURNS TEXT
LANGUAGE sql IMMUTABLE
AS $$ SELECT '₹' || TO_CHAR(COALESCE(p_amount, 0), 'FM999,999,990.00') $$;

-- Safe percentage
CREATE OR REPLACE FUNCTION fn_safe_pct(p_part NUMERIC, p_total NUMERIC)
RETURNS NUMERIC
LANGUAGE sql IMMUTABLE
AS $$ SELECT ROUND(p_part / NULLIF(p_total, 0) * 100, 1) $$;

// Part 10

What This Looks Like at Work

🎯 Pro Tip

Build your function library before you need it. The moment you write the same CASE classification twice in two different queries — that is the signal to extract it into a function. The second instance is the one that will diverge. By the time you notice drift across a dozen reports, the function library is weeks overdue. Create fn_ functions early, use them everywhere, and make schema changes in one place.

// Part 11

Interview Prep — 5 Questions With Complete Answers

// Part 12

Errors You Will Hit — And Exactly Why They Happen

What comes next

In Module 51, you learn Triggers — automatic database responses to INSERT, UPDATE, and DELETE events, with row-level and statement-level triggers, BEFORE vs AFTER timing, and audit log patterns.