Worked Example: A Custom Check

Say you want to flag any table over 50GB.

1. Write the function

CREATE OR REPLACE FUNCTION pghf.chk_custom_huge_tables(p_run_id uuid)
RETURNS pghf.check_result
LANGUAGE plpgsql
AS $$
DECLARE
    v_count    int;
    v_observed jsonb;
BEGIN
    SELECT count(*), jsonb_agg(to_jsonb(t)) INTO v_count, v_observed FROM (
        SELECT n.nspname || '.' || c.relname AS table_name,
               pg_total_relation_size(c.oid) AS total_bytes
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'r'
          AND pg_total_relation_size(c.oid) > 50 * 1024^3
        ORDER BY total_bytes DESC
    ) t;
    RETURN ROW('COLLECTED',
        jsonb_build_object('value', v_count, 'tables', COALESCE(v_observed, '[]'::jsonb)),
        NULL)::pghf.check_result;
END;
$$;

The result type here will be integer (a count), so the "value" key above is required — v_count is the untruncated total, kept separate from the (here, unlimited) detail list of individual tables. If your own check is genuinely a breakdown with no single dominant number, use result_type => 'jsonb' instead, and skip the "value" key entirely.

2. Register your namespace and category (once)

Every check lives under a namespace (up to 4 uppercase letters/digits — built-ins use PGHF) and a category within it (up to 2 uppercase letters/digits, unique per namespace). Creating a check requires both to already exist — pick your own namespace so you'll never collide with a future built-in one:

SELECT pghf.upsert_namespace('X', 'My Company Checks');
SELECT pghf.upsert_category('X', '01', 'My Custom Checks');

Both of these are upserts keyed on their code — safe to call again later just to rename something — and you only ever need to do this once per namespace or category, not once per check.

3. Create the check

SELECT pghf.create_check(
    p_namespace_code => 'X',
    p_category_code  => '01',
    p_run_number     => 1,
    p_check_name     => 'Tables over 50GB',
    p_routine        => 'pghf.chk_custom_huge_tables(uuid)'::regprocedure,
    p_result_type    => 'integer',
    p_rationale      => 'A table this large is worth knowing about proactively — '
                         'it changes maintenance-window sizing (VACUUM, REINDEX, '
                         'pg_dump) and is a common early sign of missing partitioning.',
    p_result_unit    => 'count',
    p_numeric_direction => 'lower_is_better',
    p_perfect_value  => '0'::jsonb,
    p_is_builtin     => false,
    p_description    => 'Flags any table exceeding 50GB total size.'
);
-- => 'X01-001'

The check ID is never typed by hand — it's computed automatically from the namespace code, category code, and run number (here, X + 01 + 1 → X01-001), so it can never disagree with them. The check's real, permanent identity underneath is a separate internal ID — the namespace and category are fixed forever once a check is created (nothing ever lets you change them), but the run number is mutable via pghf.update_check() (see Updating an Existing Check), and the check ID recomputes automatically the instant it changes. The run number must be between 1 and 999.

The backing function is passed as a regprocedure — PostgreSQL's own type for referring to a specific function — not as plain text, so PostgreSQL resolves it immediately: a typo'd function name or the wrong argument list fails loudly right here, not later. Creating the check also verifies the function's actual return type is pghf.check_result — simply casting to regprocedure doesn't check that on its own — so a function with the right name and arguments but the wrong return type is rejected too.

The rationale is required — a sentence or two on why this check exists: what it catches, why it matters, and what a database administrator should do about a bad result. This is distinct from the description field, which is for free-form technical notes (portability caveats, anything the check needs supplied to it) — see Metadata Columns Explained in the User Guide for the full column-by-column breakdown.

Creating a check is strictly a create: it fails if that exact namespace/category/run-number combination already exists. Calling it again for the same one is a mistake, not an update — see Updating an Existing Check next.

Creating a check also rejects the built-in PGHF namespace outright, always — that namespace is populated exclusively by the framework's own seeding function. You can still modify an existing built-in check (next section); you just can't add a brand-new row under it yourself.

Continue to Updating an Existing Check.