Stored Procedures & Functions

Overview

Implement stored procedures, functions, and triggers for business logic, data validation, and performance optimization. Covers procedure design, error handling, and performance considerations.

When to Use

• Business logic encapsulation
• Complex multi-step operations
• Data validation and constraints
• Audit trail maintenance
• Performance optimization
• Code reusability across applications
• Trigger-based automation

PostgreSQL Procedures & Functions

Simple Functions

PostgreSQL - Scalar Function:

-- Create function returning single value
CREATE OR REPLACE FUNCTION calculate_order_total(
  p_subtotal DECIMAL,
  p_tax_rate DECIMAL,
  p_shipping DECIMAL
)
RETURNS DECIMAL AS $$
BEGIN
  RETURN ROUND((p_subtotal * (1 + p_tax_rate) + p_shipping)::NUMERIC, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Use in queries
SELECT id, subtotal, calculate_order_total(subtotal, 0.08, 10) as total
FROM orders;

-- Or in application code
SELECT * FROM orders
WHERE calculate_order_total(subtotal, 0.08, 10) > 100;

PostgreSQL - Table Returning Function:

-- Return set of rows
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id UUID)
RETURNS TABLE (
  order_id UUID,
  order_date TIMESTAMP,
  total DECIMAL,
  status VARCHAR
) AS $$
BEGIN
  RETURN QUERY
  SELECT o.id, o.created_at, o.total, o.status
  FROM orders o
  WHERE o.user_id = p_user_id
  ORDER BY o.created_at DESC;
END;
$$ LANGUAGE plpgsql STABLE;

-- Use function
SELECT * FROM get_user_orders('user-123');

Stored Procedures

PostgreSQL - Procedure with OUT Parameters:

-- Stored procedure with output parameters
CREATE OR REPLACE PROCEDURE process_order(
  p_order_id UUID,
  OUT p_success BOOLEAN,
  OUT p_message VARCHAR
)
LANGUAGE plpgsql AS $$
BEGIN
  BEGIN
    -- Start transaction
    UPDATE orders SET status = 'processing' WHERE id = p_order_id;

    UPDATE inventory
    SET quantity = quantity - 1
    WHERE product_id IN (
      SELECT product_id FROM order_items WHERE order_id = p_order_id
    );

    -- Check inventory
    IF EXISTS (SELECT 1 FROM inventory WHERE quantity < 0) THEN
      RAISE EXCEPTION 'Insufficient inventory';
    END IF;

    p_success := true;
    p_message := 'Order processed successfully';
  EXCEPTION WHEN OTHERS THEN
    p_success := false;
    p_message := SQLERRM;
    -- Transaction automatically rolled back
  END;
END;
$$;

-- Call procedure
CALL process_order('order-123', success, message);
SELECT success, message;

Complex Procedure with Logic:

CREATE OR REPLACE PROCEDURE transfer_funds(
  p_from_account_id INT,
  p_to_account_id INT,
  p_amount DECIMAL,
  OUT p_success BOOLEAN,
  OUT p_error_message VARCHAR
)
LANGUAGE plpgsql AS $$
DECLARE
  v_from_balance DECIMAL;
BEGIN
  BEGIN
    -- Check balance
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE id = p_from_account_id
    FOR UPDATE;

    IF v_from_balance < p_amount THEN
      RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- Debit from account
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE id = p_from_account_id;

    -- Credit to account
    UPDATE accounts
    SET balance = balance + p_amount
    WHERE id = p_to_account_id;

    -- Log transaction
    INSERT INTO transaction_log (from_id, to_id, amount, status)
    VALUES (p_from_account_id, p_to_account_id, p_amount, 'completed');

    p_success := true;
    p_error_message := NULL;
  EXCEPTION WHEN OTHERS THEN
    p_success := false;
    p_error_message := SQLERRM;
  END;
END;
$$;

MySQL Stored Procedures

Simple Procedures

MySQL - Basic Procedure:

-- Simple procedure
DELIMITER //

CREATE PROCEDURE get_user_by_email(IN p_email VARCHAR(255))
BEGIN
  SELECT id, email, name, created_at
  FROM users
  WHERE email = p_email;
END //

DELIMITER ;

-- Call procedure
CALL get_user_by_email('[email protected]');

MySQL - Procedure with OUT Parameters:

DELIMITER //

CREATE PROCEDURE calculate_user_stats(
  IN p_user_id INT,
  OUT p_total_orders INT,
  OUT p_total_spent DECIMAL
)
BEGIN
  SELECT
    COUNT(*),
    SUM(total)
  INTO p_total_orders, p_total_spent
  FROM orders
  WHERE user_id = p_user_id AND status != 'cancelled';

  IF p_total_orders IS NULL THEN
    SET p_total_orders = 0;
    SET p_total_spent = 0;
  END IF;
END //

DELIMITER ;

-- Call procedure
CALL calculate_user_stats(123, @orders, @spent);
SELECT @orders as total_orders, @spent as total_spent;

Complex Procedures with Error Handling

MySQL - Transaction Management:

DELIMITER //

CREATE PROCEDURE create_order(
  IN p_user_id INT,
  IN p_items JSON,
  OUT p_order_id INT,
  OUT p_success BOOLEAN,
  OUT p_error VARCHAR(500)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_success = FALSE;
    SET p_error = 'Transaction failed';
  END;

  START TRANSACTION;

  -- Create order
  INSERT INTO orders (user_id, status, created_at)
  VALUES (p_user_id, 'pending', NOW());

  SET p_order_id = LAST_INSERT_ID();

  -- Add items to order (assuming items is JSON array)
  -- Would require JSON parsing in MySQL 5.7+
  -- INSERT INTO order_items (order_id, product_id, quantity)
  -- SELECT p_order_id, JSON_EXTRACT(...), ...

  -- Update inventory
  UPDATE inventory
  SET quantity = quantity - 1
  WHERE product_id IN (
    SELECT product_id FROM order_items WHERE order_id = p_order_id
  );

  -- Check inventory
  IF EXISTS (SELECT 1 FROM inventory WHERE quantity < 0) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Insufficient inventory';
  END IF;

  COMMIT;
  SET p_success = TRUE;
  SET p_error = NULL;
END //

DELIMITER ;

Triggers

PostgreSQL Triggers

Audit Trail Trigger:

-- Audit table
CREATE TABLE user_audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID,
  operation VARCHAR(10),
  old_values JSONB,
  new_values JSONB,
  changed_at TIMESTAMP DEFAULT NOW()
);

-- Trigger function
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_audit_log (user_id, operation, old_values, new_values)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    to_jsonb(OLD),
    to_jsonb(NEW)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER user_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_changes();

Update Timestamp Trigger:

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_orders_timestamp
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Validation Trigger:

CREATE OR REPLACE FUNCTION validate_order()
RETURNS TRIGGER AS $$
BEGIN
  -- Validate order total
  IF NEW.total < 0 THEN
    RAISE EXCEPTION 'Order total cannot be negative';
  END IF;

  -- Validate user exists
  IF NOT EXISTS (SELECT 1 FROM users WHERE id = NEW.user_id) THEN
    RAISE EXCEPTION 'User does not exist';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_order_trigger
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION validate_order();

MySQL Triggers

MySQL - Insert Trigger:

DELIMITER //

CREATE TRIGGER create_order_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  -- Update user statistics
  UPDATE user_stats
  SET total_orders = total_orders + 1,
      total_spent = total_spent + NEW.total
  WHERE user_id = NEW.user_id;

  -- Create audit log
  INSERT INTO audit_log (table_name, operation, record_id, timestamp)
  VALUES ('orders', 'INSERT', NEW.id, NOW());
END //

DELIMITER ;

MySQL - Update Prevention Trigger:

DELIMITER //

CREATE TRIGGER prevent_old_order_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
  IF OLD.status = 'completed' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot update completed orders';
  END IF;
END //

DELIMITER ;

Function Performance

PostgreSQL - IMMUTABLE vs STABLE vs VOLATILE:

-- IMMUTABLE: Result always same for same arguments (can be optimized)
CREATE FUNCTION calculate_tax(p_amount DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
  RETURN p_amount * 0.08;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- STABLE: Result consistent within query (can use as index)
CREATE FUNCTION get_current_year()
RETURNS INT AS $$
BEGIN
  RETURN EXTRACT(YEAR FROM CURRENT_DATE);
END;
$$ LANGUAGE plpgsql STABLE;

-- VOLATILE: Can change (function executed every time)
CREATE FUNCTION get_random_user()
RETURNS UUID AS $$
BEGIN
  RETURN (SELECT id FROM users ORDER BY RANDOM() LIMIT 1);
END;
$$ LANGUAGE plpgsql VOLATILE;

Parameter Validation

PostgreSQL - Input Validation:

CREATE OR REPLACE FUNCTION create_user(
  p_email VARCHAR,
  p_name VARCHAR
)
RETURNS UUID AS $$
DECLARE
  v_user_id UUID;
BEGIN
  -- Validate inputs
  IF p_email IS NULL OR p_email = '' THEN
    RAISE EXCEPTION 'Email cannot be empty';
  END IF;

  IF p_name IS NULL OR LENGTH(p_name) < 2 THEN
    RAISE EXCEPTION 'Name must be at least 2 characters';
  END IF;

  -- Check email format
  IF NOT p_email ~ '^\w+@\w+\.\w+$' THEN
    RAISE EXCEPTION 'Invalid email format';
  END IF;

  -- Create user
  INSERT INTO users (email, name)
  VALUES (LOWER(p_email), TRIM(p_name))
  RETURNING id INTO v_user_id;

  RETURN v_user_id;
EXCEPTION WHEN unique_violation THEN
  RAISE EXCEPTION 'Email already exists';
END;
$$ LANGUAGE plpgsql;

Testing Procedures

PostgreSQL - Test Function:

-- Test transfer_funds procedure
DO $$
DECLARE
  v_success BOOLEAN;
  v_error VARCHAR;
BEGIN
  CALL transfer_funds(1, 2, 100, v_success, v_error);
  ASSERT v_success, 'Transfer should succeed: ' || v_error;

  -- Verify transfer
  ASSERT (SELECT balance FROM accounts WHERE id = 1) = 900,
    'Account 1 balance should be 900';
  ASSERT (SELECT balance FROM accounts WHERE id = 2) = 1100,
    'Account 2 balance should be 1100';

  RAISE NOTICE 'All tests passed';
END $$;

Procedure Maintenance

PostgreSQL - Drop Procedure:

-- Drop function
DROP FUNCTION IF EXISTS calculate_order_total(DECIMAL, DECIMAL, DECIMAL);

-- Drop procedure
DROP PROCEDURE IF EXISTS process_order(UUID);

-- Drop trigger
DROP TRIGGER IF EXISTS user_audit_trigger ON users;
DROP FUNCTION IF EXISTS audit_user_changes();

Best Practices

✅ DO use procedures for complex operations ✅ DO validate inputs in procedures ✅ DO handle errors gracefully ✅ DO document procedure parameters ✅ DO test procedures thoroughly ✅ DO use transactions appropriately ✅ DO monitor procedure performance

❌ DON'T put all business logic in procedures ❌ DON'T use procedures for simple queries ❌ DON'T ignore error handling ❌ DON'T create poorly documented procedures ❌ DON'T use procedures as security layer only

Resources

• PostgreSQL Functions Documentation
• PostgreSQL PL/pgSQL Guide
• MySQL Stored Procedures
• PostgreSQL Triggers