Views, Materialized Views & Functions

A view is a saved query you can select from like a table. It hides complex joins from users and applications — for example, a report-card view.

A materialized view stores the query's result on disk, which makes expensive reports instant. Refresh it with REFRESH MATERIALIZED VIEW when the data changes.

Functions put reusable logic inside the database. Simple ones can be pure SQL; PL/pgSQL adds variables, IF/ELSE and loops. Mark functions IMMUTABLE when the same input always gives the same output, so PostgreSQL can optimise them.

views.sqlSQL

CREATE OR REPLACE FUNCTION grade_for(score numeric)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    IF score >= 75 THEN RETURN 'A';
    ELSIF score >= 65 THEN RETURN 'B';
    ELSIF score >= 45 THEN RETURN 'C';
    ELSIF score >= 30 THEN RETURN 'D';
    ELSE RETURN 'F';
    END IF;
END;
$$;

CREATE OR REPLACE VIEW report_card AS
SELECT s.id AS student_id, s.full_name, s.form, s.stream,
       sub.name AS subject, r.term, r.score, grade_for(r.score) AS grade
FROM results r
JOIN students s   ON s.id = r.student_id
JOIN subjects sub ON sub.id = r.subject_id;

SELECT subject, score, grade FROM report_card
WHERE full_name = 'Neema Kimaro' AND term = 2
ORDER BY subject;

CREATE MATERIALIZED VIEW class_summary AS
SELECT form, stream, term,
       count(DISTINCT student_id) AS students,
       round(avg(score), 1)       AS average
FROM report_card
GROUP BY form, stream, term;

SELECT * FROM class_summary ORDER BY form DESC, stream, term;
REFRESH MATERIALIZED VIEW class_summary;

-- A SQL function returning a table
CREATE OR REPLACE FUNCTION top_students(p_term int, p_limit int DEFAULT 3)
RETURNS TABLE (full_name text, average numeric)
LANGUAGE sql STABLE
AS $$
    SELECT s.full_name, round(avg(r.score), 1)
    FROM results r JOIN students s ON s.id = r.student_id
    WHERE r.term = p_term
    GROUP BY s.full_name
    ORDER BY 2 DESC
    LIMIT p_limit;
$$;

SELECT * FROM top_students(2);

Runs in your browser · PostgreSQL

Key points

Views simplify access to complex queries; they always show current data.
Materialized views cache results — remember to refresh them.
Functions keep shared logic (like grading) in one place for every app.

Exercise

Create a view fee_status with each student's name, form, balance and total paid, plus a status column ('cleared' / 'partial' / 'unpaid'). Write a function student_average(p_student_id, p_term) and use it in a query.

Show solution

Try the exercise yourself first — then compare your approach with this one.

The view joins each student to their total payments (with coalesce for students who paid nothing) and derives the status with CASE. The function wraps a parameterised average so any query can call it.

fee-status.sqlSQL

CREATE OR REPLACE VIEW fee_status AS
SELECT s.full_name,
       s.form,
       s.fee_balance,
       coalesce(sum(p.amount), 0) AS total_paid,
       CASE
           WHEN s.fee_balance = 0 THEN 'cleared'
           WHEN coalesce(sum(p.amount), 0) > 0 THEN 'partial'
           ELSE 'unpaid'
       END AS status
FROM students s
LEFT JOIN payments p ON p.student_id = s.id
GROUP BY s.id;

SELECT * FROM fee_status ORDER BY status, full_name;

CREATE OR REPLACE FUNCTION student_average(p_student_id bigint, p_term int)
RETURNS numeric
LANGUAGE sql STABLE
AS $$
    SELECT round(avg(score), 1) FROM results WHERE student_id = p_student_id AND term = p_term;
$$;

SELECT full_name, student_average(id, 1) AS term1, student_average(id, 2) AS term2
FROM students
WHERE form = 4
ORDER BY term2 DESC NULLS LAST;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding