Catalog: Checks

pghf.copy_check(p_source_check_id text, p_namespace_code text, p_category_code text, p_run_number int, p_routine regprocedure, p_check_name text DEFAULT NULL, p_result_type text DEFAULT NULL, p_rationale text DEFAULT NULL, p_result_unit text DEFAULT NULL, p_bool_pass_value boolean DEFAULT NULL, p_numeric_direction text DEFAULT NULL, p_perfect_value jsonb DEFAULT NULL, p_doc_url text DEFAULT NULL, p_description text DEFAULT NULL, p_min_pg_version int DEFAULT NULL, p_max_pg_version int DEFAULT NULL, p_requires_topology text DEFAULT NULL, p_how_to_fix text DEFAULT NULL, p_execution_mode text DEFAULT NULL, p_source_table text DEFAULT NULL, p_copy_thresholds boolean DEFAULT false)

The way to customise a built-in check. Since the update function locks every built-in check's definition (see below) — and re-seeding would overwrite a hand-edit anyway — this clones an existing check under a new namespace/category/run-number combination that you then own and can edit freely. It delegates the actual insert to pghf.create_check(), so it automatically inherits that function's safety checks (the destination must not already exist, the namespace and category must exist and be active, and you can't create directly under the built-in namespace), and always registers the copy as a non-built-in check.

Parameter	Type	Mandatory	Default	Description
p_source_check_id	text	Yes	—	Check ID to copy from. Must exist.
p_namespace_code	text	Yes	—	Destination namespace (cannot be the built-in PGHF namespace).
p_category_code	text	Yes	—	Destination category.
p_run_number	int	Yes	—	Destination run number, 1–999.
p_routine	regprocedure	Yes	—	Not inheritable — a check's backing function must be unique to it, so the source's function can never be reused. You must already have written and registered the new check's own function first. (regprocedure is PostgreSQL's type for referring directly to a specific function — see the Glossary.)
p_check_name	text	No	empty = inherit from source	Override the display name.
p_result_type	text	No	empty = inherit from source	Override the result type.
p_rationale	text	No	empty = inherit from source	Override the rationale.
p_result_unit	text	No	empty = inherit from source	Override the unit.
p_bool_pass_value	boolean	No	empty = inherit from source	Override the boolean pass value.
p_numeric_direction	text	No	empty = inherit from source	Override the numeric direction.
p_perfect_value	jsonb	No	empty = inherit from source	Override the perfect value.
p_doc_url	text	No	empty = inherit from source	Override the documentation link.
p_description	text	No	empty = inherit from source	Override the description.
p_min_pg_version	int	No	empty = inherit from source	Override the minimum PostgreSQL major version required.
p_max_pg_version	int	No	empty = inherit from source	Override the maximum PostgreSQL major version supported.
p_requires_topology	text	No	empty = inherit from source	Override the topology requirement ('any' / 'primary' / 'standby'). Unlike create_check() below, leaving this empty here means "inherit," not "unconstrained" — see the note under update_check().
p_how_to_fix	text	No	empty = inherit from source	Override the remediation guidance.
p_execution_mode	text	No	empty = inherit from source	Override 'polled' / 'on_event' — see On-Event Checks. Same "empty means inherit, not 'polled'" rule as p_requires_topology above.
p_source_table	text	No	empty = inherit from source	Override the table this event is about — see p_execution_mode on pghf.create_check() below. Same inherit-not-default rule.
p_copy_thresholds	boolean	No	false	When true, also copies the source check's threshold onto the new check. Reports a notice, not an error, if the copied threshold has a custom evaluator attached (it almost certainly still refers to the source check specifically) or if the source has no threshold to copy.

Returns: text — the new check ID. Only a role explicitly granted permission can call this. Does not copy suite membership — add the new check to a suite yourself.

pghf.create_check(p_namespace_code text, p_category_code text, p_run_number int, p_check_name text, p_routine regprocedure, p_result_type text, p_rationale text, p_result_unit text DEFAULT NULL, p_bool_pass_value boolean DEFAULT NULL, p_numeric_direction text DEFAULT NULL, p_perfect_value jsonb DEFAULT NULL, p_is_builtin boolean DEFAULT false, p_doc_url text DEFAULT NULL, p_description text DEFAULT NULL, p_min_pg_version int DEFAULT NULL, p_max_pg_version int DEFAULT NULL, p_requires_topology text DEFAULT 'any', p_how_to_fix text DEFAULT NULL, p_execution_mode text DEFAULT 'polled', p_source_table text DEFAULT NULL)

The only way to add a new check. The check ID is never typed by hand — it's computed automatically from the namespace code, category code, and run number. Fails if that exact combination already exists (use pghf.update_check() to modify one instead). Rejects the built-in PGHF namespace outright — that namespace is populated exclusively by the framework's own seeding function; use pghf.copy_check() to customise an existing built-in check instead.

Parameter	Type	Mandatory	Default	Description
p_namespace_code	text	Yes	—	Must already exist and be active; cannot be the built-in PGHF namespace.
p_category_code	text	Yes	—	Must already exist under that namespace and be active.
p_run_number	int	Yes	—	1–999; unique within its namespace and category.
p_check_name	text	Yes	—	Display name.
p_routine	regprocedure	Yes	—	The function backing this check — must have exactly the signature described in Check Implementation Functions.
p_result_type	text	Yes	—	'boolean', 'integer', 'numeric', 'text', or 'jsonb'.
p_rationale	text	Yes	—	Why this check matters — required, non-empty.
p_result_unit	text	No	empty	Unit of the result value.
p_bool_pass_value	boolean	No	empty	Only meaningful when the result type is 'boolean'.
p_numeric_direction	text	No	empty	Only meaningful when the result type is 'integer' or 'numeric'.
p_perfect_value	jsonb	No	empty	A known-ideal value, purely descriptive.
p_is_builtin	boolean	No	false	Whether this is a framework-shipped built-in (normally left false for your own checks).
p_doc_url	text	No	empty	A reference documentation link.
p_description	text	No	empty	A free-form technical note.
p_min_pg_version	int	No	empty = no floor	Minimum PostgreSQL major version (e.g. 17) this check qualifies for. Checked by pghf.execute_check_run() itself, before the check is ever invoked — see Engine-level qualification.
p_max_pg_version	int	No	empty = no ceiling	Maximum PostgreSQL major version this check qualifies for.
p_requires_topology	text	No	'any'	'any' / 'primary' / 'standby'. Excludes this check on a non-matching node before it's ever invoked, same mechanism as the version range.
p_how_to_fix	text	No	empty	Optional remediation guidance for this check's finding, distinct from p_rationale (why it matters, not what to do about it). Populated in Markdown on every built-in check.
p_execution_mode	text	No	'polled'	'polled' (reachable from a suite, invoked on a cadence) or 'on_event' (invoked standalone, exactly once, by pghf.execute_check()/pghf.execute_check_event() — typically from a trigger, told directly what severity to record). An 'on_event' check can never be added to a suite — see On-Event Checks.
p_source_table	text	Required iff p_execution_mode = 'on_event'	empty	The table this event is about, e.g. 'pgrelay.log' — free text, not validated against the catalog (the table may belong to an extension not yet installed). Purely descriptive; raises if left empty on an 'on_event' check. Always optional, and normally left empty, on a 'polled' check.

Returns: text — the new check ID. Only a role explicitly granted permission — whichever should be trusted to author checks in your deployment — can call this.

pghf.get_check(p_check_id text)

Returns one check record by check ID.

Parameter	Type	Mandatory	Default	Description
p_check_id	text	Yes	—	Check to look up.

Returns: the check record. Raises an error if not found.

pghf.list_checks(p_namespace_code text DEFAULT NULL, p_category_code text DEFAULT NULL, p_include_retired boolean DEFAULT false)

Lists check records. For a right-anchored wildcard pattern instead (for example 'PGHF01-*'), use pghf.add_checks_matching() in preview mode (see Suites) — this function deliberately doesn't duplicate that matching logic.

Parameter	Type	Mandatory	Default	Description
p_namespace_code	text	No	empty = every namespace	Scope to one namespace.
p_category_code	text	No	empty = every category	Scope to one category code (independent of namespace).
p_include_retired	boolean	No	false	true also includes retired checks.

Returns: zero or more check records, ordered by check ID.

pghf.update_check(p_check_id text, p_check_name text, p_routine regprocedure, p_result_type text, p_rationale text, p_run_number int DEFAULT NULL, p_result_unit text DEFAULT NULL, p_bool_pass_value boolean DEFAULT NULL, p_numeric_direction text DEFAULT NULL, p_perfect_value jsonb DEFAULT NULL, p_is_builtin boolean DEFAULT false, p_doc_url text DEFAULT NULL, p_description text DEFAULT NULL, p_min_pg_version int DEFAULT NULL, p_max_pg_version int DEFAULT NULL, p_requires_topology text DEFAULT 'any', p_how_to_fix text DEFAULT NULL, p_execution_mode text DEFAULT 'polled', p_source_table text DEFAULT NULL, p_is_active boolean DEFAULT true, p_retired_reason text DEFAULT NULL)

The only way to modify an existing check, including activating or inactivating it — there's no separate retire/deactivate function. Fails if the check doesn't already exist (use pghf.create_check() to add a new one). This is a full replace, not a partial patch — every definition field must be re-supplied on every call, not just the one that changed. This includes p_execution_mode/p_source_table on an existing 'on_event' check — omit them (letting the defaults 'polled'/empty apply) on an unrelated update and you silently flip the check back to 'polled', same as forgetting to restate any other field.

Built-in definition lock: for a check under the reserved PGHF namespace, every definition field — including the minimum/maximum version, required topology, execution mode, source table, and how-to-fix fields below — is compared against the check's current stored value; if any of them would actually change, this raises an error instead of applying the edit, and points you towards pghf.copy_check() instead. Activating/inactivating and the retirement reason are not definition fields, and are always accepted regardless of namespace — you can always activate or inactivate a built-in check, you just can't rewrite what it measures. Setting a threshold is likewise entirely unaffected by this lock. See Resetting to Defaults for why this restriction exists.

A blank value means something different for three of these fields. For every other field on this function, leaving it blank means "no value" (no unit, no numeric direction). For the minimum/maximum version and required-topology fields, leaving them blank means "no constraint on this axis" — a real, common, intentional state, not an omission.

Parameter	Type	Mandatory	Default	Description
p_check_id	text	Yes	—	The check to modify. Must already exist.
p_check_name	text	Yes	—	Display name (full replace).
p_routine	regprocedure	Yes	—	Backing function (full replace).
p_result_type	text	Yes	—	Result type (full replace).
p_rationale	text	Yes	—	Rationale (full replace, required non-empty).
p_run_number	int	No	empty = unchanged	The only mutable identity field; changing it recomputes the check ID.
p_result_unit	text	No	empty	Unit — passing nothing clears it, since this is a full replace, not a patch.
p_bool_pass_value	boolean	No	empty	Boolean pass value.
p_numeric_direction	text	No	empty	Numeric direction.
p_perfect_value	jsonb	No	empty	Perfect value.
p_is_builtin	boolean	No	false	Re-registering an existing built-in as non-built-in is refused.
p_doc_url	text	No	empty	Documentation link.
p_description	text	No	empty	Description.
p_min_pg_version	int	No	empty = no floor	Minimum PostgreSQL major version this check qualifies for. See the blank-value note above.
p_max_pg_version	int	No	empty = no ceiling	Maximum PostgreSQL major version this check qualifies for.
p_requires_topology	text	No	'any'	'any' / 'primary' / 'standby'.
p_how_to_fix	text	No	empty	Remediation guidance, distinct from p_rationale (why it matters, not what to do about it).
p_execution_mode	text	No	'polled'	'polled' / 'on_event' (full replace, not blank-means-inherit — see pghf.copy_check() for the inherit variant). Switching an already-suite-member check to 'on_event' also removes it from every suite it belonged to, same cleanup p_is_active => false performs — a check this function leaves 'on_event' is never left as a suite member.
p_source_table	text	Required iff p_execution_mode = 'on_event'	empty	Same rule as on pghf.create_check() — full replace, not inherited.
p_is_active	boolean	No	true	false inactivates: records the retirement (and its reason) and removes the check from every suite it belonged to. true (the default) clears that state. Exempt from the built-in definition lock and from the inactive-namespace/category guard.
p_retired_reason	text	No	empty	Recorded only when p_is_active is false.

Returns: text — the check's (possibly recomputed) check ID. Only a role explicitly granted permission can call this.

Continue to Suites.