Day 7 of 10 · Harbour Mutual

Functions and triggers

Put logic inside the database with PL/pgSQL: functions that compute a business definition once, and triggers that enforce rules and keep an audit trail as data changes.

Theory for today: Premiums, Losses and claims, Functions and triggers.

The lesson

Functions

A function takes parameters and returns a value, like ROUND or AGE, but written by you. Simple ones can be plain SQL; PL/pgSQL, PostgreSQL's own language, adds variables, IF and loops.

CREATE OR REPLACE FUNCTION policy_years(p_start DATE, p_end DATE) RETURNS NUMERIC
LANGUAGE SQL AS $$
    SELECT ROUND((p_end - p_start + 1) / 365.0, 2)
$$;

SELECT policy_number, policy_years(start_date, end_date)
FROM policies
LIMIT 5;

$$ quotes the function's body; parameters are named with a p_ prefix so they cannot be mistaken for columns. Try it in the playground

CREATE OR REPLACE FUNCTION claim_size(p_amount NUMERIC) RETURNS TEXT
LANGUAGE PLPGSQL AS $$
BEGIN
    IF p_amount >= 20000 THEN
        RETURN 'large';
    ELSIF p_amount >= 2000 THEN
        RETURN 'medium';
    END IF;
    RETURN 'small';
END
$$;

SELECT claim_number, reserve, claim_size(reserve)
FROM claims
WHERE status = 'open'
LIMIT 5;

PL/pgSQL: BEGIN ... END, IF ... ELSIF ... END IF, and RETURN. Try it in the playground

Triggers

A trigger runs a function automatically when rows are inserted, updated or deleted. A BEFORE trigger can inspect the new row, NEW, and refuse it with RAISE EXCEPTION; an AFTER trigger sees the change done, and can record it. In an update, OLD is the row as it was.

CREATE OR REPLACE FUNCTION no_empty_notes() RETURNS TRIGGER
LANGUAGE PLPGSQL AS $$
BEGIN
    IF TRIM(NEW.reason) = '' THEN
        RAISE EXCEPTION 'A watchlist entry needs a reason';
    END IF;
    RETURN NEW;
END
$$;

create TRIGGER no_empty_notes BEFORE INSERT OR UPDATE ON watchlist
FOR EACH ROW EXECUTE FUNCTION no_empty_notes();

A trigger function returns TRIGGER; a BEFORE trigger returns NEW to let the row through. (Needs the watchlist from Day 1.) Try it in the playground

Remove them with DROP TRIGGER IF EXISTS ... ON TABLE; and DROP FUNCTION IF EXISTS ...;.

A CHECK constraint sees one row; a trigger can look at other tables. Use a trigger for a rule such as 'a claim's loss must fall inside its policy's period'.

Briefs from the business

0 of 4 done

Write SQL to answer each brief, run it, then check your answer against Harbour Mutual's. Your database is the same one as in the playground.

Starting PostgreSQL in your browser…

Brief 7.1 · Earned premium as a function

Write a function earned_premium(p_policy_id INT, p_as_of DATE) that returns the premium a policy has earned from its start up to and including p_as_of, to 2 decimals: the premium, times the days covered from the start to the earlier of p_as_of and the end date, divided by 365 (or by the trip's length for a single trip, product 5). Before the start it is 0.

Tables: policies

Brief 7.2 · No claims outside the policy period

Write a trigger on claims that refuses a claim whose loss date is not within its policy's period (from the start date to the end date, inclusive), whether the claim is inserted or updated.

Tables: claims, policies

Brief 7.3 · An audit trail of reserves

Auditors want every change to a claim's reserve recorded. Create a table reserve_changes with the columns change_id (serial primary key), claim_id, old_reserve, new_reserve and changed_on (a date, 31 December 2025 unless given), and a trigger that adds a row whenever an update changes a claim's reserve.

Tables: claims, reserve_changes (yours)

Brief 7.4 · How often does a customer claim?

Write a function customer_claim_count(p_customer_id INT) that returns how many claims a customer has made across all their policies (0 if none).

Tables: claims, policies

4 briefs to go today.

Day 8: Query optimisation