How to fix 20000: case_not_found in PostgreSQL

-- Example of problematic CASE without ELSE
CREATE OR REPLACE FUNCTION get_status_label(status_code integer)
RETURNS text AS $$
BEGIN
  RETURN CASE status_code
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    WHEN 3 THEN 'Pending'
    -- No ELSE clause - will fail for status_code = 4
  END;
END;
$$ LANGUAGE plpgsql;

-- This will fail:
SELECT get_status_label(4);  -- Error: 20000: case_not_found

-- Fix 1: Add a default ELSE value
CREATE OR REPLACE FUNCTION get_status_label(status_code integer)
RETURNS text AS $$
BEGIN
  RETURN CASE status_code
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    WHEN 3 THEN 'Pending'
    ELSE 'Unknown'  -- Default for unexpected values
  END;
END;
$$ LANGUAGE plpgsql;

-- Fix 2: Raise a custom error with more context
CREATE OR REPLACE FUNCTION get_status_label(status_code integer)
RETURNS text AS $$
BEGIN
  RETURN CASE status_code
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    WHEN 3 THEN 'Pending'
    ELSE
      RAISE EXCEPTION 'Invalid status code: %', status_code
        USING HINT = 'Valid codes are 1, 2, 3';
  END;
END;
$$ LANGUAGE plpgsql;

-- Problem: NULL values won't match WHEN NULL
CREATE OR REPLACE FUNCTION categorize_value(val integer)
RETURNS text AS $$
BEGIN
  RETURN CASE val
    WHEN 1 THEN 'Low'
    WHEN 2 THEN 'Medium'
    WHEN 3 THEN 'High'
    -- WHEN NULL THEN 'Unknown' -- This won't work as expected
    ELSE 'Unexpected'  -- NULL will fall here, not cause case_not_found
  END;
END;
$$ LANGUAGE plpgsql;

-- Better: Use searched CASE for NULL handling
CREATE OR REPLACE FUNCTION categorize_value(val integer)
RETURNS text AS $$
BEGIN
  RETURN CASE
    WHEN val IS NULL THEN 'Unknown'
    WHEN val = 1 THEN 'Low'
    WHEN val = 2 THEN 'Medium'
    WHEN val = 3 THEN 'High'
    ELSE 'Out of range'
  END;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION process_order_status(status text)
RETURNS text AS $$
BEGIN
  -- Validate input first
  IF status NOT IN ('new', 'processing', 'shipped', 'delivered', 'cancelled') THEN
    RAISE EXCEPTION 'Invalid order status: "%". Valid values: new, processing, shipped, delivered, cancelled', status;
  END IF;

  RETURN CASE status
    WHEN 'new' THEN 'Order received'
    WHEN 'processing' THEN 'In progress'
    WHEN 'shipped' THEN 'Shipped to customer'
    WHEN 'delivered' THEN 'Delivery confirmed'
    WHEN 'cancelled' THEN 'Order cancelled'
    -- No ELSE needed since we validated above
  END;
END;
$$ LANGUAGE plpgsql;

-- Test the fixed function
SELECT get_status_label(1) = 'Active' AS test1;
SELECT get_status_label(2) = 'Inactive' AS test2;
SELECT get_status_label(3) = 'Pending' AS test3;
SELECT get_status_label(4) = 'Unknown' AS test4;  -- Previously would fail
SELECT get_status_label(NULL) = 'Unknown' AS test5;

-- For functions that should raise errors on invalid input:
DO $$
BEGIN
  BEGIN
    PERFORM get_status_label(999);  -- Should raise exception
    RAISE EXCEPTION 'Expected exception not raised';
  EXCEPTION
    WHEN OTHERS THEN
      -- Expected behavior
      RAISE NOTICE 'Correctly raised exception: %', SQLERRM;
  END;
END $$;

-- Check existing data for uncovered values
SELECT DISTINCT status_code 
FROM orders 
WHERE status_code NOT IN (1, 2, 3);  -- Find values needing handling