sql-advanced-features

SQL Advanced Features

Essential patterns for database objects, procedural logic, and transaction management.

Core Principles

1. Push logic close to the data - Views, procedures, and functions reduce network round-trips and enforce consistency
2. Wrap multi-step operations in transactions - Guarantee atomicity for related changes
3. Prefer declarative over procedural - Use set-based SQL before resorting to cursors or loops
4. Parameterize all dynamic SQL - Never concatenate user input into SQL strings
5. Design for concurrency - Choose the weakest isolation level that guarantees correctness

Views

-- Encapsulate business logic in a reusable view
CREATE OR REPLACE VIEW active_customer_orders AS
SELECT
    c.customer_id,
    c.customer_name,
    c.email,
    o.order_id,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER (PARTITION BY c.customer_id) AS lifetime_value
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
  AND o.order_date >= CURRENT_DATE - INTERVAL '1 year';

See view-patterns.md for:

• Simple and complex view definitions
• Updatable views and WITH CHECK OPTION
• Security-barrier views and row-level access
• View dependency management

Materialized Views

-- Pre-compute expensive aggregations
CREATE MATERIALIZED VIEW monthly_revenue_summary AS
SELECT
    DATE_TRUNC('month', order_date) AS revenue_month,
    product_category,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value
FROM orders o
INNER JOIN products p ON o.product_id = p.product_id
WHERE order_status = 'completed'
GROUP BY DATE_TRUNC('month', order_date), product_category
WITH DATA;

CREATE UNIQUE INDEX idx_monthly_rev ON monthly_revenue_summary (revenue_month, product_category);

See materialized-view-patterns.md for:

• Refresh strategies (full, concurrent, incremental)
• Auto-refresh with pg_cron and scheduled jobs
• Indexed materialized views (SQL Server)