Views Functions and Triggers

Overview

View: A stored query that behaves like a table; no data stored, re-run on every read
Materialized View: A stored query result — fast reads, stale until REFRESH
Function: Reusable logic in SQL or PL/pgSQL; Procedure (PG 11+) can COMMIT inside
Trigger: Function that runs automatically on INSERT/UPDATE/DELETE/TRUNCATE
Sample Data: E-commerce Practice Queries

Views

Creating and Using

CREATE OR REPLACE VIEW order_summary AS
SELECT o.id          AS order_id,
       o.user_id,
       u.full_name,
       o.status,
       o.created_at,
       sum(oi.quantity * oi.unit_price) AS total,
       count(*)                         AS line_items
FROM orders o
JOIN users u        ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, u.full_name;
 
SELECT full_name, count(*) AS orders, sum(total) AS spend
FROM order_summary
WHERE status <> 'cancelled'
GROUP BY full_name
ORDER BY spend DESC;

Properties

Always Fresh: The query runs each time; the planner inlines the view and pushes filters into it
Abstraction / Security: Expose a subset of columns/rows; grant on the view, not the base table
CREATE OR REPLACE: Can add columns at the end, but can’t drop/rename/retype — use DROP VIEW + CREATE
Dependencies: Base tables can’t drop a column a view uses (DROP … CASCADE drops the view too)

Updatable Views and CHECK OPTION

Auto-updatable: Simple views (one table, no aggregates/DISTINCT/GROUP BY/LIMIT) accept INSERT/UPDATE/DELETE
WITH CHECK OPTION: Rejects writes that would produce rows invisible through the view
security_invoker (PG 15+): Run with the caller’s permissions/RLS instead of the view owner’s

CREATE OR REPLACE VIEW active_products AS
SELECT id, sku, name, price, stock, is_active
FROM products
WHERE is_active
WITH CHECK OPTION;
 
BEGIN;
UPDATE active_products SET price = price + 1 WHERE sku = 'ACC-001';    -- writes through to products
-- UPDATE active_products SET is_active = false WHERE sku = 'ACC-001';
--   ERROR: new row violates check option for view "active_products"
ROLLBACK;

Materialized Views

Creating and Refreshing

CREATE MATERIALIZED VIEW IF NOT EXISTS monthly_revenue AS
SELECT date_trunc('month', o.created_at)::date AS month,
       count(DISTINCT o.id)                    AS orders,
       sum(oi.quantity * oi.unit_price)        AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY 1
WITH DATA;
 
-- REFRESH ... CONCURRENTLY requires a UNIQUE index on the matview
CREATE UNIQUE INDEX IF NOT EXISTS monthly_revenue_month_idx ON monthly_revenue (month);
 
SELECT * FROM monthly_revenue ORDER BY month;
 
REFRESH MATERIALIZED VIEW monthly_revenue;                -- blocks readers while rebuilding
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;   -- readers keep seeing old data; diff-applies changes

REFRESH vs REFRESH CONCURRENTLY

Aspect	REFRESH	REFRESH CONCURRENTLY
Lock	`ACCESS EXCLUSIVE` — blocks `SELECT`	`EXCLUSIVE` — `SELECT` still works
Requirement	None	A `UNIQUE` index on plain columns; matview already populated
Speed	Faster (truncate + insert)	Slower (computes diff, updates changed rows)
Use When	Off-hours / nobody reading	Dashboards read it continuously

View vs Materialized View vs Table

Aspect	View	Materialized View	Summary Table + Triggers
Freshness	Always current	As of last refresh	Real-time
Read Speed	Same as underlying query	Fast (indexed)	Fast
Write Cost	None	Refresh cost (whole query)	Per-write trigger cost
Indexes	No (uses base table’s)	Yes	Yes
Scheduling	n/a	`pg_cron` / app job	n/a

Functions

SQL Functions

LANGUAGE sql: Body is plain SQL; simple ones can be inlined into the calling query
Volatility: IMMUTABLE (same input → same output forever; required for expression indexes), STABLE (consistent within a statement, may read tables), VOLATILE (default; random(), writes)
PARALLEL SAFE: Mark it so parallel plans can use it
SQL-standard Body (PG 14+): BEGIN ATOMIC … END — parsed at creation, dependencies tracked

CREATE OR REPLACE FUNCTION order_total(p_order_id bigint)
RETURNS numeric
LANGUAGE sql STABLE PARALLEL SAFE
AS $$
    SELECT COALESCE(sum(quantity * unit_price), 0)
    FROM order_items
    WHERE order_id = p_order_id
$$;
 
SELECT id, status, order_total(id) FROM orders WHERE user_id = 1 ORDER BY id;
 
-- Set-returning function: behaves like a parameterised view
CREATE OR REPLACE FUNCTION user_orders(p_user_id bigint)
RETURNS TABLE (order_id bigint, status text, total numeric)
LANGUAGE sql STABLE
AS $$
    SELECT o.id, o.status, order_total(o.id)
    FROM orders o
    WHERE o.user_id = p_user_id
    ORDER BY o.created_at
$$;
 
SELECT * FROM user_orders(2);

PL/pgSQL Functions

Use When: You need variables, IF, loops, exceptions, dynamic SQL
RAISE: NOTICE for debugging, EXCEPTION to abort with an error
Performance: Each statement inside is planned (and cached); avoid row-by-row loops when one set-based query works

CREATE OR REPLACE FUNCTION apply_discount(p_sku text, p_percent numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
    v_new_price numeric;
BEGIN
    IF p_percent <= 0 OR p_percent >= 90 THEN
        RAISE EXCEPTION 'discount % out of range', p_percent USING ERRCODE = 'check_violation';
    END IF;
 
    UPDATE products
    SET price = round(price * (1 - p_percent / 100), 2)
    WHERE sku = p_sku
    RETURNING price INTO v_new_price;
 
    IF NOT FOUND THEN
        RAISE EXCEPTION 'unknown sku %', p_sku;
    END IF;
 
    RAISE NOTICE 'new price for % is %', p_sku, v_new_price;
    RETURN v_new_price;
END;
$$;
 
BEGIN;
SELECT apply_discount('LAP-001', 10);          -- 899.10
-- SELECT apply_discount('LAP-001', 95);       -- ERROR: discount 95 out of range
ROLLBACK;

Procedures

-- Procedures are CALLed and may COMMIT between batches (not allowed in functions)
CREATE OR REPLACE PROCEDURE archive_failed_payments(p_batch int DEFAULT 1000)
LANGUAGE plpgsql
AS $$
DECLARE
    v_rows int;
BEGIN
    LOOP
        DELETE FROM payments
        WHERE id IN (SELECT id FROM payments WHERE status = 'failed' LIMIT p_batch);
        GET DIAGNOSTICS v_rows = ROW_COUNT;
        EXIT WHEN v_rows = 0;
        COMMIT;                                    -- release locks / WAL per batch
    END LOOP;
END;
$$;
-- CALL archive_failed_payments(500);   -- must run OUTSIDE an explicit BEGIN block:
--   inside one it fails with ERROR: invalid transaction termination

Triggers

Concepts

Option	Choices	Notes
Timing	`BEFORE`, `AFTER`, `INSTEAD OF` (views)	`BEFORE` can modify `NEW` or skip the row (return NULL)
Event	`INSERT`, `UPDATE [OF cols]`, `DELETE`, `TRUNCATE`
Level	`FOR EACH ROW`, `FOR EACH STATEMENT`	Statement triggers can use transition tables (`REFERENCING NEW TABLE AS …`)
Condition	`WHEN (OLD.x IS DISTINCT FROM NEW.x)`	Skip calling the function when nothing relevant changed
Variables	`NEW`, `OLD`, `TG_OP`, `TG_TABLE_NAME`	`NEW` is NULL for DELETE, `OLD` for INSERT

Example 1: Maintain updated_at

ALTER TABLE products ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now();
 
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;                                    -- BEFORE trigger: return the (modified) row
END;
$$;
 
CREATE OR REPLACE TRIGGER products_set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)                -- skip no-op updates
EXECUTE FUNCTION set_updated_at();

Example 2: Audit Log

CREATE TABLE IF NOT EXISTS audit_log (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name text NOT NULL,
    op         text NOT NULL,
    row_id     bigint,
    old_row    jsonb,
    new_row    jsonb,
    changed_by text NOT NULL DEFAULT current_user,
    changed_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, op, row_id, old_row, new_row)
    VALUES (TG_TABLE_NAME,
            TG_OP,
            COALESCE(NEW.id, OLD.id),
            CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
            CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
    RETURN NULL;                                   -- AFTER trigger: return value ignored
END;
$$;
 
CREATE OR REPLACE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_trigger();
 
BEGIN;
UPDATE orders SET status = 'cancelled' WHERE id = 15;
UPDATE products SET stock = stock - 1 WHERE sku = 'BOK-001';
 
SELECT op, row_id, old_row ->> 'status' AS old_status, new_row ->> 'status' AS new_status
FROM audit_log;
SELECT sku, updated_at > created_at AS touched FROM products WHERE sku = 'BOK-001';
ROLLBACK;

Example 3: Enforce a Business Rule

-- Prevent changing items of an order that has already shipped
CREATE OR REPLACE FUNCTION forbid_shipped_item_changes()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM orders
               WHERE id = COALESCE(NEW.order_id, OLD.order_id)
                 AND status IN ('shipped', 'delivered')) THEN
        RAISE EXCEPTION 'order % is already %', COALESCE(NEW.order_id, OLD.order_id), 'shipped/delivered';
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$;
 
CREATE OR REPLACE TRIGGER order_items_guard
BEFORE INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION forbid_shipped_item_changes();
 
-- DELETE FROM order_items WHERE order_id = 1;
--   ERROR: order 1 is already shipped/delivered
BEGIN;
DELETE FROM order_items WHERE order_id = 10 RETURNING *;     -- pending order: allowed
ROLLBACK;

Trigger Pitfalls

Hidden Logic: Behaviour invisible to app developers — document triggers and keep them small
Performance: Row-level triggers fire per row; bulk loads slow down dramatically (use statement-level + transition tables, or disable during loads)
Recursion: A trigger updating its own table can re-fire itself — guard with WHEN or pg_trigger_depth()
Ordering: Multiple triggers on the same event fire in alphabetical name order
Not Fired By: TRUNCATE doesn’t fire row-level DELETE triggers; COPY does fire row triggers

Inspecting Objects

-- psql: \dv  \dm  \df  \dS+ products (shows triggers)
SELECT viewname FROM pg_views WHERE schemaname = 'public';
SELECT matviewname, ispopulated FROM pg_matviews;
SELECT tgname, tgrelid::regclass AS on_table FROM pg_trigger WHERE NOT tgisinternal ORDER BY tgname;
SELECT proname, prokind FROM pg_proc WHERE pronamespace = 'public'::regnamespace ORDER BY proname;

Interview Questions

“View vs materialized view?”: View re-runs the query; matview stores results and must be refreshed
“Refresh a matview without blocking readers?”: REFRESH MATERIALIZED VIEW CONCURRENTLY + a unique index
“Function vs procedure?”: Functions return values and run inside the caller’s transaction; procedures are CALLed and can COMMIT
“IMMUTABLE vs STABLE vs VOLATILE?”: Determines caching and index eligibility — only IMMUTABLE functions can be used in index expressions
“BEFORE vs AFTER trigger?”: BEFORE can change/skip the row; AFTER sees the final row and is used for side effects like audit logs
“Why are triggers controversial?”: Hidden side effects, harder debugging, per-row overhead

Summary

Views: Saved queries, always fresh, can be updatable with WITH CHECK OPTION
Materialized Views: Cached results, indexable; REFRESH … CONCURRENTLY needs a unique index
Functions: SQL for simple/inlinable logic, PL/pgSQL for control flow; set volatility correctly
Procedures: CALL, can commit in loops — batch maintenance jobs
Triggers: updated_at, audit logs, invariants — keep them small, documented and set-based where possible

Kaushik's Notes

Explorer

Views Functions and Triggers

Overview

Views

Creating and Using

Properties

Updatable Views and CHECK OPTION

Materialized Views

Creating and Refreshing

REFRESH vs REFRESH CONCURRENTLY

View vs Materialized View vs Table

Functions

SQL Functions

PL/pgSQL Functions

Procedures

Triggers

Concepts

Example 1: Maintain updated_at

Example 2: Audit Log

Example 3: Enforce a Business Rule

Trigger Pitfalls

Inspecting Objects

Interview Questions

Summary

Graph View

Table of Contents

Backlinks