Views & Procedural SQL

Chapter 9: Views & Procedural SQL

SQL is not limited to data retrieval; it provides mechanisms for creating Virtual Relations (Views) and implementing server-side business logic through Procedural Extensions (Stored Procedures, Functions, and Triggers). These tools allow for centralizing logic within the database, reducing network round-trips and enforcing security perimeters.

I. Virtual Relations: Views

A View is a saved SQL query that can be treated as a table. It does not store data physically (unless it is a Materialized View).

1. Standard Views

Used for security (hiding sensitive columns) or simplifying complex joins for application developers.

  • Updatability: A view is generally only updatable if it maps to a single base table and does not contain aggregations or DISTINCT clauses.
-- Creating a Security View (Hiding salary)
CREATE VIEW public_employee_info AS
SELECT employee_id, first_name, last_name, department_id
FROM employees;

2. Materialized Views

A Materialized View stores the query result physically on disk.

  • Use Case: Heavy analytical queries that take seconds/minutes to run.
  • Trade-off: Data becomes stale. It must be manually or periodically Refreshed.

II. Procedural SQL: Logic in the Kernel

Modern RDBMSs extend SQL with procedural constructs (loops, variables, if/else). Common standards include PL/pgSQL (PostgreSQL), T-SQL (SQL Server), and PL/SQL (Oracle).

1. Stored Procedures and Functions

  • Procedures: Executed via CALL. Can return multiple result sets and perform DML.
  • Functions: Executed within a SELECT. Must return a value and are generally restricted from performing DML (except in specific contexts).
-- Example: Logic to promote an employee
CREATE PROCEDURE promote_employee(emp_id BIGINT, raise_pct DECIMAL)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees 
    SET salary = salary * (1 + raise_pct),
        status = 'PROMOTED'
    WHERE employee_id = emp_id;
    
    INSERT INTO audit_log(action, target_id) 
    VALUES ('PROMOTION', emp_id);
END;
$$;

2. Triggers: Automated Side Effects

A trigger is a procedural block that executes automatically in response to a specific DML event (INSERT, UPDATE, DELETE) on a table.

DML OpTrigger EngineBEFORE / AFTERFOR EACH ROWAudit/Sync/Validation

III. Production Anti-Patterns

  • Business Logic Overload: Implementing complex, multi-thousand-line business rules in stored procedures. This makes version control, testing, and horizontal scaling of the application layer significantly harder.
  • Recursive Triggers: A trigger on Table A that updates Table B, which has a trigger that updates Table A. This can lead to an infinite loop and crash the server.
  • Missing View Indices: Querying a complex view and joining it with another table without realizing the view lacks an index on the join key, forcing a Cartesian product.

IV. Performance Bottlenecks

  • Context Switching: In some engines, switching from the SQL executor to the Procedural (PL) engine for every row in a large result set adds significant CPU overhead.
  • Trigger Latency: A single INSERT that triggers five complex AFTER INSERT tasks can turn a sub-millisecond write into a 500ms blocking operation.
  • Materialized View Refresh: Refreshing a multi-terabyte materialized view can saturate disk IOPS and lock the view for hours if "Concurrent Refresh" is not supported.
PreviousTake Quiz