Views, Stored Functions, and Triggers
Objective. Students will create updatable and non-updatable views, write PL/pgSQL functions with parameters, and implement a simple audit trigger.
This lesson, its worked examples and its practice questions has not yet been verified by an industry-certified professional or by a certification or approval body. It is a study draft and may contain errors. Do not use it as instructions for work on a job site or on live equipment. Always follow your instructor, your supervising professional, and current official references and regulations.
VoTechWorks program approval and accreditation are still in progress. Credentials are issued only by the certifying bodies named in each program, after their own exams.
Views, Stored Functions, and Triggers in PostgreSQL
This lesson moves beyond basic queries into three powerful database objects: views, stored functions, and triggers. Together they let you encapsulate logic, enforce consistency, and automate record-keeping inside the database itself.
---
Views
A view is a named query stored in the database. When you query a view, PostgreSQL executes the underlying SELECT and returns the result as if it were a table. Views serve several purposes: hiding complex joins from application code, restricting which columns a user can see, and presenting aggregated summaries.
```sql
-- Simple column-subset view
CREATE VIEW book_titles AS
SELECT isbn, title, author
FROM books;
```
This view exposes only three columns. Users who SELECT from `book_titles` never see cost or inventory fields.
Updatable vs. Non-Updatable Views
PostgreSQL allows INSERT, UPDATE, and DELETE through a view under specific conditions:
- The view queries exactly one base table.
- It contains no DISTINCT, no GROUP BY or HAVING, no aggregate functions, no set operations (UNION, INTERSECT, EXCEPT), and no window functions.
- The SELECT list includes no subquery in the column list.
A join view or an aggregation view fails these tests and is therefore non-updatable by default. You can still make a non-updatable view support writes by creating an `INSTEAD OF` trigger or using a rewrite rule, but that is advanced usage.
```sql
-- Join view (non-updatable without extra work)
CREATE VIEW loan_details AS
SELECT l.loan_id, m.full_name, b.title, l.due_date
FROM loans l
JOIN members m ON m.member_id = l.member_id
JOIN books b ON b.isbn = l.isbn;
-- Aggregation view (non-updatable)
CREATE VIEW loans_per_member AS
SELECT member_id, COUNT(*) AS total_loans
FROM loans
GROUP BY member_id;
```
Always test updateability by attempting a simple UPDATE and reading the error message. Document which rule is violated.
---
PL/pgSQL Stored Functions
PL/pgSQL is PostgreSQL's built-in procedural language. It adds variables, conditionals, loops, and exception handling to standard SQL.
A function that returns a table is especially useful for parameterized reports:
```sql
CREATE OR REPLACE FUNCTION overdue_loans(
p_start DATE,
p_end DATE
)
RETURNS TABLE (
loan_id INT,
member_id INT,
isbn TEXT,
due_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT l.loan_id, l.member_id, l.isbn, l.due_date
FROM loans l
WHERE l.due_date BETWEEN p_start AND p_end
AND l.returned_on IS NULL;
END;
$$;
```
Call the function like a table:
```sql
SELECT * FROM overdue_loans('2024-01-01', '2024-03-31');
```
Always verify function output against an equivalent manual query run in the same session. If the row counts differ, inspect the WHERE clause and NULL handling.
Dollar-quoting (`$$`) is the standard way to delimit the function body in PostgreSQL because it avoids conflicts with single quotes inside the body.
---
Triggers and Audit Logging
A trigger is a function that PostgreSQL calls automatically when a specified event (INSERT, UPDATE, DELETE, or TRUNCATE) occurs on a table. Every trigger calls a trigger function — a special function that returns type `TRIGGER`.
The Audit Pattern
Audit logging is one of the most common trigger use cases. Before any UPDATE on `books`, capture what the row looked like and what it will become:
```sql
-- 1. Create the audit table
CREATE TABLE IF NOT EXISTS audit_log (
log_id SERIAL PRIMARY KEY,
changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
old_row JSONB,
new_row JSONB
);
-- 2. Create the trigger function
CREATE OR REPLACE FUNCTION log_book_update()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO audit_log (changed_at, old_row, new_row)
VALUES (
CURRENT_TIMESTAMP,
row_to_json(OLD)::JSONB,
row_to_json(NEW)::JSONB
);
RETURN NEW; -- required for BEFORE trigger to allow the update
END;
$$;
-- 3. Attach the trigger
CREATE TRIGGER books_audit
BEFORE UPDATE ON books
FOR EACH ROW
EXECUTE FUNCTION log_book_update();
```
BEFORE vs. AFTER triggers: A `BEFORE` trigger runs before the row is written and can modify `NEW` or cancel the operation by returning NULL. An `AFTER` trigger runs after the write is committed to the row but before the transaction ends; it cannot change `NEW`.
OLD and NEW: Inside a trigger function, `OLD` holds the existing row values and `NEW` holds the incoming values. On a DELETE, `NEW` is null. On an INSERT, `OLD` is null.
---
Workplace and Data-Safety Notes
- Never test triggers or destructive DDL on a production database without a verified backup. Run all lab work on your local development instance.
- Views that expose PII (Personally Identifiable Information) should be granted only to roles that need them. Use `GRANT SELECT ON view_name TO role_name;` and revoke from PUBLIC.
- Audit tables grow without bound. In real systems, schedule archival jobs or use table partitioning. For this lab, monitor row counts during testing.
- Always end your psql session with `\q` and close pgAdmin when leaving a shared workstation. Never leave an open superuser session unattended.
Key terms10
Materials: Library dataset, PostgreSQL PL/pgSQL documentation, psql, pgAdmin, instructor view and trigger reference examples
Check: Students submit .sql files containing the view definitions, function, and trigger; instructor executes a test script that calls the function and fires the trigger; pass = all objects execute without error and return correct results.
Practice check8
Answer each question, then check it. Nothing is recorded; this is your own practice.
