PostgreSQL Stored Procedures

-- Create stored procedure
CREATE OR REPLACE PROCEDURE process_monthly_billing()
LANGUAGE plpgsql
AS $$
DECLARE
  billing_month DATE := DATE_TRUNC('month', CURRENT_DATE);
  processed_count INT;
BEGIN
  -- Insert billing records
  INSERT INTO billing (customer_id, billing_month, amount)
  SELECT
    customer_id,
    billing_month,
    SUM(amount)
  FROM orders
  WHERE order_date >= billing_month
    AND order_date < billing_month + INTERVAL '1 month'
  GROUP BY customer_id
  ON CONFLICT (customer_id, billing_month)
  DO UPDATE SET amount = EXCLUDED.amount;

  GET DIAGNOSTICS processed_count = ROW_COUNT;
  RAISE NOTICE 'Processed % billing records', processed_count;

  COMMIT;
END;
$$;

-- Call procedure
CALL process_monthly_billing();

User-Defined Functions

-- Scalar function
CREATE OR REPLACE FUNCTION calculate_discount(
  total_amount DECIMAL,
  customer_tier VARCHAR
) RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
DECLARE
  discount_rate DECIMAL;
BEGIN
  discount_rate := CASE customer_tier
    WHEN 'platinum' THEN 0.15
    WHEN 'gold' THEN 0.10
    WHEN 'silver' THEN 0.05
    ELSE 0.00
  END;

  -- Apply volume discount
  IF total_amount > 10000 THEN
    discount_rate := discount_rate + 0.05;
  END IF;

  RETURN total_amount * discount_rate;
END;
$$;

-- Use in query
SELECT
  order_id,
  total_amount,
  calculate_discount(total_amount, customer_tier) AS discount
FROM orders;

Table-Returning Functions

-- Function returning table
CREATE OR REPLACE FUNCTION get_employee_hierarchy(manager_id_param INT)
RETURNS TABLE (
  employee_id INT,
  employee_name VARCHAR,
  level INT,
  path TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  WITH RECURSIVE hierarchy AS (
    SELECT
      e.employee_id,
      e.name::VARCHAR AS employee_name,
      0 AS level,
      e.name::TEXT AS path
    FROM employees e
    WHERE e.employee_id = manager_id_param

    UNION ALL

    SELECT
      e.employee_id,
      e.name::VARCHAR,
      h.level + 1,
      h.path || ' → ' || e.name::TEXT
    FROM employees e
    INNER JOIN hierarchy h ON e.manager_id = h.employee_id
  )
  SELECT * FROM hierarchy;
END;
$$;

-- Use function
SELECT * FROM get_employee_hierarchy(1);

BigQuery Stored Procedures

-- BigQuery stored procedure
CREATE OR REPLACE PROCEDURE `project.dataset.process_data`(
  IN start_date DATE,
  IN end_date DATE
)
BEGIN
  -- Create temporary table
  CREATE TEMPORARY TABLE temp_results AS
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM `project.dataset.orders`
  WHERE order_date BETWEEN start_date AND end_date
  GROUP BY customer_id;

  -- Insert results
  INSERT INTO `project.dataset.customer_totals`
  SELECT * FROM temp_results
  ON DUPLICATE KEY UPDATE
    total = VALUES(total);
END;

-- Call procedure
CALL `project.dataset.process_data`('2024-01-01', '2024-01-31');

Exception Handling

-- Robust error handling
CREATE OR REPLACE PROCEDURE safe_transfer(
  from_account INT,
  to_account INT,
  amount DECIMAL
)
LANGUAGE plpgsql
AS $$
DECLARE
  from_balance DECIMAL;
BEGIN
  -- Check balance
  SELECT balance INTO from_balance
  FROM accounts
  WHERE account_id = from_account
  FOR UPDATE;

  IF from_balance < amount THEN
    RAISE EXCEPTION 'Insufficient funds: % < %', from_balance, amount;
  END IF;

  -- Perform transfer
  UPDATE accounts SET balance = balance - amount
  WHERE account_id = from_account;

  UPDATE accounts SET balance = balance + amount
  WHERE account_id = to_account;

  INSERT INTO transfers (from_account, to_account, amount)
  VALUES (from_account, to_account, amount);

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE NOTICE 'Transfer failed: %', SQLERRM;
    RAISE;
END;
$$;

Dynamic SQL in Procedures

-- Dynamic SQL with parameterization
CREATE OR REPLACE PROCEDURE dynamic_query(
  table_name TEXT,
  column_name TEXT,
  search_value TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
  result RECORD;
  query TEXT;
BEGIN
  query := format(
    'SELECT * FROM %I WHERE %I = $1',
    table_name,
    column_name
  );

  FOR result IN EXECUTE query USING search_value
  LOOP
    RAISE NOTICE 'Found: %', result;
  END LOOP;
END;
$$;

Function Overloading

-- Overloaded functions
CREATE FUNCTION calculate_tax(amount DECIMAL) RETURNS DECIMAL
LANGUAGE plpgsql AS $$
BEGIN
  RETURN amount * 0.08;
END;
$$;

CREATE FUNCTION calculate_tax(amount DECIMAL, tax_rate DECIMAL) RETURNS DECIMAL
LANGUAGE plpgsql AS $$
BEGIN
  RETURN amount * tax_rate;
END;
$$;

-- Call different overloads
SELECT calculate_tax(100);        -- Uses first function
SELECT calculate_tax(100, 0.10);  -- Uses second function

Cursors in Procedures

-- Process rows with cursor
CREATE OR REPLACE PROCEDURE process_orders()
LANGUAGE plpgsql
AS $$
DECLARE
  order_cursor CURSOR FOR
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE status = 'pending'
    FOR UPDATE;
  order_record RECORD;
BEGIN
  FOR order_record IN order_cursor
  LOOP
    -- Process each order
    UPDATE orders
    SET status = 'processing',
        processed_at = CURRENT_TIMESTAMP
    WHERE CURRENT OF order_cursor;

    -- Update customer stats
    UPDATE customer_stats
    SET total_orders = total_orders + 1,
        total_spent = total_spent + order_record.total_amount
    WHERE customer_id = order_record.customer_id;
  END LOOP;

  COMMIT;
END;
$$;

Package-like Organization

-- Group related functions
CREATE SCHEMA billing;

CREATE OR REPLACE FUNCTION billing.calculate_invoice_total(
  customer_id INT,
  start_date DATE,
  end_date DATE
) RETURNS DECIMAL
LANGUAGE plpgsql AS $$
DECLARE
  total DECIMAL;
BEGIN
  SELECT COALESCE(SUM(amount), 0) INTO total
  FROM invoices
  WHERE customer_id = $1
    AND invoice_date BETWEEN $2 AND $3;
  RETURN total;
END;
$$;

CREATE OR REPLACE PROCEDURE billing.generate_invoices(
  billing_date DATE
)
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO invoices (customer_id, amount, invoice_date)
  SELECT
    customer_id,
    billing.calculate_invoice_total(customer_id, billing_date - INTERVAL '1 month', billing_date),
    billing_date
  FROM customers
  WHERE status = 'active';
END;
$$;

Follow-Up Questions

1. When should you use stored procedures vs application code?
2. How do you handle transactions within stored procedures?
3. Explain the difference between functions and procedures in PostgreSQL.
4. How would you implement retry logic in a stored procedure?
5. What's the best approach for testing stored procedures?
6. How do you version control stored procedures?