majiayu000 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('john@example.com');
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