Implement stored procedures and functions for database logic. Use when creating reusable database routines, complex queries, or server-side calculations.
Implement stored procedures, functions, and triggers for business logic, data validation, and performance optimization. Covers procedure design, error handling, and performance considerations.
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;
Detailed implementations in the references/ directory:
| Guide | Contents |
|---|---|
| Simple Functions | Simple Functions |
| Stored Procedures | Stored Procedures |
| Simple Procedures | Simple Procedures |
| Complex Procedures with Error Handling | Complex Procedures with Error Handling |
| PostgreSQL Triggers | PostgreSQL Triggers |
| MySQL Triggers | MySQL Triggers |