Stored Procedures

// Part 01

What a Stored Procedure Is

-- Create a stored procedure
CREATE OR REPLACE PROCEDURE procedure_name(
  param1  data_type,
  param2  data_type DEFAULT default_value,
  INOUT  result_param data_type DEFAULT NULL  -- INOUT for output values
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_variable   data_type;           -- local variable declaration
  v_another    data_type := 0;      -- with initial value
BEGIN
  -- SQL statements
  SELECT col INTO v_variable FROM table WHERE condition;

  -- Control flow
  IF v_variable > 100 THEN
    UPDATE table SET col = col * 1.1;
  ELSE
    RAISE NOTICE 'Value is %', v_variable;   -- log message
  END IF;

  -- Exception handling
  EXCEPTION
    WHEN unique_violation THEN
      RAISE EXCEPTION 'Duplicate entry: %', SQLERRM;
    WHEN OTHERS THEN
      RAISE;   -- re-raise any other exception
END;
$$;

-- Call a stored procedure
CALL procedure_name(arg1, arg2);

// Part 02

Stored Procedure vs Function vs View — The Differences

// Part 03

Your First Stored Procedure — Variables and Basic Logic

-- Procedure: calculate and upgrade a customer's loyalty tier
-- based on their delivered order history
CREATE OR REPLACE PROCEDURE sp_upgrade_loyalty_tier(
  p_customer_id  INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_lifetime_value  NUMERIC;
  v_current_tier    TEXT;
  v_new_tier        TEXT;
BEGIN
  -- Step 1: get current tier and lifetime value
  SELECT
    c.loyalty_tier,
    COALESCE(SUM(o.total_amount), 0)
  INTO
    v_current_tier,
    v_lifetime_value
  FROM customers AS c
  LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
    AND o.order_status = 'Delivered'
  WHERE c.customer_id = p_customer_id
  GROUP BY c.loyalty_tier;

  -- Step 2: determine the earned tier
  v_new_tier := CASE
    WHEN v_lifetime_value >= 5000 THEN 'Platinum'
    WHEN v_lifetime_value >= 2000 THEN 'Gold'
    WHEN v_lifetime_value >= 500  THEN 'Silver'
    ELSE 'Bronze'
  END;

  -- Step 3: update only if tier changed
  IF v_new_tier != v_current_tier THEN
    UPDATE customers
    SET loyalty_tier = v_new_tier
    WHERE customer_id = p_customer_id;

    RAISE NOTICE 'Customer % upgraded: % → %',
      p_customer_id, v_current_tier, v_new_tier;
  ELSE
    RAISE NOTICE 'Customer % tier unchanged: %',
      p_customer_id, v_current_tier;
  END IF;
END;
$$;

-- Call it:
CALL sp_upgrade_loyalty_tier(1);
CALL sp_upgrade_loyalty_tier(42);

// Part 04

Parameters — IN, OUT, and INOUT

-- IN (default): value passed into the procedure — read-only inside
-- OUT: value returned from the procedure — write-only inside
-- INOUT: both passed in AND returned — readable and writable

-- Example: procedure that returns computed values via INOUT/OUT
CREATE OR REPLACE PROCEDURE sp_get_store_stats(
  p_store_id      TEXT,                 -- IN: which store
  INOUT p_orders  INTEGER DEFAULT 0,    -- OUT: order count
  INOUT p_revenue NUMERIC DEFAULT 0     -- OUT: total revenue
)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT
    COUNT(*),
    ROUND(SUM(total_amount), 2)
  INTO
    p_orders,
    p_revenue
  FROM orders
  WHERE store_id     = p_store_id
    AND order_status = 'Delivered';
END;
$$;

-- Call and read the INOUT outputs:
DO $$
DECLARE
  v_orders  INTEGER;
  v_revenue NUMERIC;
BEGIN
  CALL sp_get_store_stats('ST001', v_orders, v_revenue);
  RAISE NOTICE 'ST001 — Orders: %, Revenue: ₹%', v_orders, v_revenue;
END;
$$;

-- Note: in PostgreSQL, functions are often preferred over procedures
-- when returning values — functions work naturally in SELECT queries
-- Procedures with INOUT are useful when transaction control is also needed

// Part 05

Control Flow — IF, CASE, Loops

CREATE OR REPLACE PROCEDURE sp_apply_discount(
  p_order_id  INTEGER,
  p_tier      TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_discount  NUMERIC;
  v_original  NUMERIC;
BEGIN
  -- Get original amount
  SELECT total_amount INTO v_original
  FROM orders WHERE order_id = p_order_id;

  -- Determine discount by loyalty tier
  IF p_tier = 'Platinum' THEN
    v_discount := 0.20;
  ELSIF p_tier = 'Gold' THEN
    v_discount := 0.15;
  ELSIF p_tier = 'Silver' THEN
    v_discount := 0.10;
  ELSE
    v_discount := 0.05;
  END IF;

  -- Apply discount
  UPDATE orders
  SET total_amount = ROUND(v_original * (1 - v_discount), 2)
  WHERE order_id = p_order_id;

  RAISE NOTICE 'Order %: ₹% → ₹% (% discount applied)',
    p_order_id,
    v_original,
    ROUND(v_original * (1 - v_discount), 2),
    (v_discount * 100)::TEXT || '%';
END;
$$;

CREATE OR REPLACE PROCEDURE sp_batch_upgrade_all_customers()
LANGUAGE plpgsql
AS $$
DECLARE
  v_customer   RECORD;        -- holds one row at a time
  v_count      INTEGER := 0;
BEGIN
  -- FOR loop over a query result — iterate every customer
  FOR v_customer IN
    SELECT customer_id FROM customers ORDER BY customer_id
  LOOP
    -- Call the upgrade procedure for each customer
    CALL sp_upgrade_loyalty_tier(v_customer.customer_id);
    v_count := v_count + 1;
  END LOOP;

  RAISE NOTICE 'Processed % customers', v_count;
END;
$$;

-- WHILE loop example:
CREATE OR REPLACE PROCEDURE sp_retry_failed_payments(p_max_attempts INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
  v_attempt INTEGER := 0;
  v_pending INTEGER;
BEGIN
  LOOP
    -- Count remaining failed payments
    SELECT COUNT(*) INTO v_pending
    FROM payment_log WHERE status = 'failed';

    EXIT WHEN v_pending = 0 OR v_attempt >= p_max_attempts;

    -- Process one batch of retries
    UPDATE payment_log SET status = 'retrying'
    WHERE status = 'failed'
    LIMIT 100;

    v_attempt := v_attempt + 1;
    RAISE NOTICE 'Retry attempt %, remaining: %', v_attempt, v_pending;
  END LOOP;
END;
$$;

// Part 06

Exception Handling — RAISE and EXCEPTION Blocks

CREATE OR REPLACE PROCEDURE sp_place_order(
  p_customer_id  INTEGER,
  p_store_id     TEXT,
  p_product_id   INTEGER,
  p_quantity     INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_unit_price   NUMERIC;
  v_in_stock     BOOLEAN;
  v_order_id     INTEGER;
BEGIN
  -- Validate product
  SELECT unit_price, in_stock
  INTO   v_unit_price, v_in_stock
  FROM   products
  WHERE  product_id = p_product_id;

  -- Not found: FOUND is FALSE if SELECT INTO returned no rows
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Product % does not exist', p_product_id
      USING ERRCODE = 'P0001';   -- custom error code
  END IF;

  IF NOT v_in_stock THEN
    RAISE EXCEPTION 'Product % is out of stock', p_product_id
      USING ERRCODE = 'P0002';
  END IF;

  -- Insert order
  INSERT INTO orders (customer_id, store_id, order_date, order_status, total_amount)
  VALUES (p_customer_id, p_store_id, CURRENT_DATE, 'Processing',
          v_unit_price * p_quantity)
  RETURNING order_id INTO v_order_id;

  -- Insert order item
  INSERT INTO order_items (order_id, product_id, quantity, unit_price, line_total)
  VALUES (v_order_id, p_product_id, p_quantity, v_unit_price,
          v_unit_price * p_quantity);

  RAISE NOTICE 'Order % placed successfully for customer %', v_order_id, p_customer_id;

EXCEPTION
  WHEN foreign_key_violation THEN
    RAISE EXCEPTION 'Invalid customer_id or store_id: %', SQLERRM;
  WHEN unique_violation THEN
    RAISE EXCEPTION 'Duplicate order detected: %', SQLERRM;
  WHEN OTHERS THEN
    -- Log the error and re-raise
    RAISE NOTICE 'Unexpected error in sp_place_order: %', SQLERRM;
    RAISE;   -- re-raises the original exception
END;
$$;

// Part 07

Transaction Control Inside Procedures

-- Procedure that processes orders in batches
-- Each batch commits independently — no single giant transaction
CREATE OR REPLACE PROCEDURE sp_process_pending_orders(
  p_batch_size  INTEGER DEFAULT 100
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_order       RECORD;
  v_processed   INTEGER := 0;
  v_failed      INTEGER := 0;
BEGIN
  FOR v_order IN
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE order_status = 'Processing'
    ORDER BY order_date
    LIMIT p_batch_size
  LOOP
    BEGIN
      -- Try to process each order
      UPDATE orders
      SET order_status = 'Confirmed'
      WHERE order_id = v_order.order_id;

      INSERT INTO processing_log (order_id, processed_at, status)
      VALUES (v_order.order_id, NOW(), 'success');

      v_processed := v_processed + 1;

      -- COMMIT each order individually — not waiting for the whole batch
      COMMIT;

    EXCEPTION WHEN OTHERS THEN
      -- One order failing does not stop the batch
      ROLLBACK;
      v_failed := v_failed + 1;

      INSERT INTO error_log (order_id, error_msg, logged_at)
      VALUES (v_order.order_id, SQLERRM, NOW());
      COMMIT;   -- commit the error log entry
    END;
  END LOOP;

  RAISE NOTICE 'Batch complete: % processed, % failed', v_processed, v_failed;
END;
$$;

-- Note: COMMIT/ROLLBACK inside procedures is PostgreSQL 11+
-- MySQL stored procedures also support COMMIT/ROLLBACK
-- Cannot use COMMIT/ROLLBACK inside PostgreSQL FUNCTIONS

// Part 08

Stored Functions vs Stored Procedures

-- FUNCTION: returns a value — usable in SELECT/WHERE/JOIN
CREATE OR REPLACE FUNCTION fn_customer_lifetime_value(
  p_customer_id INTEGER
) RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
  v_total NUMERIC;
BEGIN
  SELECT COALESCE(SUM(total_amount), 0)
  INTO   v_total
  FROM   orders
  WHERE  customer_id   = p_customer_id
    AND  order_status  = 'Delivered';
  RETURN v_total;
END;
$$;

-- Function is callable in any SQL context:
SELECT customer_id, fn_customer_lifetime_value(customer_id) AS ltv
FROM customers;

WHERE fn_customer_lifetime_value(customer_id) > 1000

-- PROCEDURE: performs actions — called with CALL only
CREATE OR REPLACE PROCEDURE sp_upgrade_all_tiers()
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE customers AS c
  SET loyalty_tier = CASE
    WHEN fn_customer_lifetime_value(c.customer_id) >= 5000 THEN 'Platinum'
    WHEN fn_customer_lifetime_value(c.customer_id) >= 2000 THEN 'Gold'
    WHEN fn_customer_lifetime_value(c.customer_id) >= 500  THEN 'Silver'
    ELSE 'Bronze'
  END;
  -- Can COMMIT here (procedure can manage transactions)
END;
$$;

CALL sp_upgrade_all_tiers();

-- Function that returns a table (like a parameterised view)
CREATE OR REPLACE FUNCTION fn_store_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    INTEGER,
  daily_revenue  NUMERIC,
  avg_order      NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    o.order_date,
    COUNT(*)::INTEGER,
    ROUND(SUM(o.total_amount), 2),
    ROUND(AVG(o.total_amount), 2)
  FROM orders AS o
  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
  ORDER BY o.order_date;
END;
$$;

-- Call exactly like a table:
SELECT * FROM fn_store_report('ST001');
SELECT * FROM fn_store_report('ST001', '2024-01-01', '2024-01-31');

-- Join to other tables:
SELECT s.city, r.daily_revenue
FROM fn_store_report('ST001') AS r
JOIN stores AS s ON s.store_id = 'ST001';

// Part 09

Managing Procedures — List, View, Alter, Drop

-- List all procedures in the current database (PostgreSQL)
SELECT
  routine_name,
  routine_type,
  data_type AS return_type,
  external_language AS language
FROM information_schema.routines
WHERE routine_schema = 'public'
  AND routine_type IN ('PROCEDURE', 'FUNCTION')
ORDER BY routine_type, routine_name;

-- View a procedure's definition
SELECT prosrc
FROM pg_proc
WHERE proname = 'sp_upgrade_loyalty_tier';

-- Or use pg_get_functiondef:
SELECT pg_get_functiondef(oid)
FROM pg_proc
WHERE proname = 'sp_upgrade_loyalty_tier';

-- Update a procedure (CREATE OR REPLACE — no DROP needed)
CREATE OR REPLACE PROCEDURE sp_upgrade_loyalty_tier(p_customer_id INTEGER)
LANGUAGE plpgsql AS $$
BEGIN
  -- ... updated logic ...
END;
$$;

-- Drop a procedure
DROP PROCEDURE sp_upgrade_loyalty_tier(INTEGER);
DROP PROCEDURE IF EXISTS sp_upgrade_loyalty_tier(INTEGER);

-- Drop with CASCADE (also drops dependent triggers/rules)
DROP PROCEDURE sp_upgrade_loyalty_tier(INTEGER) CASCADE;

-- Procedure permissions
GRANT EXECUTE ON PROCEDURE sp_upgrade_loyalty_tier(INTEGER) TO app_role;
REVOKE EXECUTE ON PROCEDURE sp_upgrade_loyalty_tier(INTEGER) FROM PUBLIC;

// Part 10

When to Use Stored Procedures vs Application Logic

// Part 11

What This Looks Like at Work

CREATE OR REPLACE PROCEDURE sp_nightly_seller_tier_update(
  p_month_start  DATE DEFAULT DATE_TRUNC('month', CURRENT_DATE)::DATE
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_updated      INTEGER;
  v_run_id       INTEGER;
  v_month_end    DATE;
BEGIN
  v_month_end := (p_month_start + INTERVAL '1 month' - INTERVAL '1 day')::DATE;

  -- Log the run start
  INSERT INTO batch_log (job_name, started_at, status)
  VALUES ('nightly_tier_update', NOW(), 'running')
  RETURNING id INTO v_run_id;

  -- Step 1: compute monthly revenue per seller and update tier
  -- Single set-based UPDATE — no row-by-row looping needed
  WITH seller_revenue AS (
    SELECT
      seller_id,
      ROUND(SUM(order_amount), 2)  AS monthly_revenue
    FROM seller_orders
    WHERE order_date BETWEEN p_month_start AND v_month_end
      AND order_status = 'Delivered'
    GROUP BY seller_id
  )
  UPDATE sellers AS s
  SET
    current_month_revenue = sr.monthly_revenue,
    seller_tier = CASE
      WHEN sr.monthly_revenue >= 500000 THEN 'Diamond'
      WHEN sr.monthly_revenue >= 100000 THEN 'Platinum'
      WHEN sr.monthly_revenue >= 25000  THEN 'Gold'
      WHEN sr.monthly_revenue >= 5000   THEN 'Silver'
      ELSE 'Bronze'
    END,
    tier_updated_at = NOW()
  FROM seller_revenue AS sr
  WHERE s.seller_id = sr.seller_id;

  GET DIAGNOSTICS v_updated = ROW_COUNT;

  -- Step 2: credit cashback to Diamond and Platinum sellers
  UPDATE seller_wallets AS w
  SET balance = balance + (s.current_month_revenue * 0.02)
  FROM sellers AS s
  WHERE w.seller_id = s.seller_id
    AND s.seller_tier IN ('Diamond', 'Platinum')
    AND s.tier_updated_at >= NOW() - INTERVAL '1 minute';

  -- Step 3: commit and update log
  UPDATE batch_log
  SET
    finished_at    = NOW(),
    rows_processed = v_updated,
    status         = 'success'
  WHERE id = v_run_id;

  COMMIT;

  RAISE NOTICE 'Tier update complete: % sellers updated in % → %',
    v_updated, p_month_start, v_month_end;

EXCEPTION WHEN OTHERS THEN
  UPDATE batch_log
  SET status = 'failed', error_msg = SQLERRM, finished_at = NOW()
  WHERE id = v_run_id;
  COMMIT;
  RAISE;
END;
$$;

-- Scheduled with pg_cron every night at 2:00 AM:
-- SELECT cron.schedule('nightly-tier-update', '0 2 * * *',
--   $$CALL sp_nightly_seller_tier_update()$$);

🎯 Pro Tip

The biggest performance win from stored procedures is eliminating row-by-row processing. If your application fetches rows, processes each one in a loop, and issues individual UPDATEs — you can almost always replace that with a single set-based UPDATE inside a stored procedure. Set-based operations in the database are orders of magnitude faster than row-by-row loops because they avoid network round trips and allow the query engine to optimise the entire operation as a unit.

// Part 12

Interview Prep — 5 Questions With Complete Answers

// Part 13

Errors You Will Hit — And Exactly Why They Happen

What comes next

In Module 50, you learn User-Defined Functions — scalar functions, table-valued functions, SQL functions vs PL/pgSQL functions, IMMUTABLE vs STABLE vs VOLATILE, and building a reusable function library.