Function Reference¶

The complete function surface, including profile management, maintenance, and grants — for the sender-only subset most application code needs, see the User Guide's function reference instead.

All functions are SECURITY DEFINER with SET search_path = pgrelay_notifier, pg_temp. "Sender"/"Admin" mark which grant helper confers access; "Processor" marks the pgrelay-role interface set; "Superuser" marks functions granted to nobody by default.

Interface functions (Processor)¶

Function	Returns	Contract
`interface_version()`	integer	Returns 1. Immutable. Also executable by any role with schema `USAGE` (compatibility probe).
`fetch(p_id bigint)`	`TABLE(transport, profile jsonb, message jsonb, debug)`	Zero rows for an unknown id — never raises. Side-effect free (`STABLE`). Applies the sender override; strips base64 line wraps; emits the forward-compatible `priority` and per-attachment `inline` keys. For `transport = 'webhook'`, `message` is instead the provider-rendered request body (built by `_render_webhook_body()`), POSTed verbatim by the Processor. `profile` may contain `_env:VAR_NAME` secret references.
`set_status(p_id, p_status, p_detail, p_provider_ref, p_elapsed_ms, p_attempt)`	void	Appends to `status_history` unconditionally; updates the notification's latest-status columns when the id and status are recognised. Tolerates unknown ids and statuses without raising.
`debug_log(p_id, p_step, p_detail)`	void	Appends a trace row. Exception-guarded: never raises.
`classify_webhook_response(p_notification_id, p_http_status, p_response_headers jsonb, p_response_body)`	`TABLE(status, provider_ref, detail)`	Webhook transport only (pg_relay ≥ 1.2; preflight check `webhook:function`). Interprets one HTTP response — `sent`/`retry`/`failed` — via fixed-CASE dispatch to the profile's provider classifier. Side-effect free; zero rows for an unknown id, which the Processor resolves as a permanent failure. Headers arrive flat with lower-cased keys; `p_http_status` 0 means no response was received.

Profile management (Admin)¶

Function	Returns	Notes
`create_profile(p_profile_name, p_transport, p_profile, p_channel, p_debug, p_notes, p_max_attach_mb, p_max_attach_number, p_send_from, p_reply_to, p_delay_seconds, p_provider)`	bigint	Validates the profile block, listing every issue in one error; secret-bearing keys (`password`, the smtp `oauth2` block's `client_secret`/`refresh_token`, m365 `client_secret`, webhook `auth.secret`) must be `_env:VAR_NAME` references, and a webhook `body_merge`, when present, must be a JSON object (any keys; values of any JSON type, strings may be `_env:` references — never resolved database-side). `p_provider` is required for (and only valid with) `transport = 'webhook'` — it selects the provider adapter (currently `slack`, `resend`, `telnyx`, `pagerduty`; a `pagerduty` profile additionally requires `body_merge.routing_key` as an `_env:` reference). Case-insensitive unique names. Attachment limits default from `profile_defaults` for the transport; explicit arguments win. `p_delay_seconds` sets the profile's dispatch-time delay default; NULL (default) means no delay.
`update_profile(p_profile_name, p_profile, p_channel, p_debug, p_active, p_notes, p_max_attach_mb, p_max_attach_number, p_send_from, p_reply_to, p_delay_seconds)`	void	NULL keeps the current value; empty string clears `p_send_from`/`p_reply_to`; `0` clears `p_delay_seconds`. `p_profile`, when given, replaces the whole connection block (secrets included) and is re-validated. Transport and provider cannot change.
`list_profile_defaults()`	TABLE	The per-transport seed values new profiles start from.
`update_profile_defaults(p_transport, p_max_attach_mb, p_max_attach_number)`	void	Edits a transport's seeds (NULL keeps current). Applies to profiles created afterwards only.
`delete_profile(p_profile_name)`	void	Refuses while notifications reference it (deactivate instead to keep history).
`pause(p_profile_name, p_pause_until)`	void	Pauses sending (rejected at `dispatch()`) on one profile or every profile (`p_profile_name` NULL). `p_pause_until` omitted: `now() + 7 days`. In the past: the profile ends the call not paused, never raises.
`resume(p_profile_name, p_resume_at)`	void	Resumes one profile or every profile. `p_resume_at` omitted: immediately. Given: stored as the new `paused_until` verbatim.
`get_profile(p_profile_name)`	TABLE	One profile; zero rows when absent.
`list_profiles()`	TABLE	All profiles, ordered case-insensitively by name.
`validate_profile(p_transport, p_profile)`	SETOF text	Dry-run: one row per issue, zero rows when valid.

Validation rules — SMTP: host and from required; port 1–65535; security ∈ starttls/tls/none; auth ∈ plain/login/none/oauth2 (defaulted from username); plain/login ⇒ username + password (must be an _env: reference); oauth2 ⇒ an oauth2 object with grant_type ∈ client_credentials (default; ⇒ username, client_id, tenant_id, scope, _env: client_secret) or refresh_token (⇒ username, client_id, token_url, _env: client_secret and refresh_token) — see SMTP Endpoints. M365: tenant_id, client_id, sender, and client_secret (must be an _env: reference) required; save_to_sent_items boolean. Webhook: url required, http(s)://; auth object required with style ∈ bearer_header/custom_header/basic_auth and secret (an _env: reference, or the literal placeholder unused for endpoints that authenticate elsewhere); custom_header ⇒ header_name, basic_auth ⇒ username; method a non-empty string; headers an object of strings; body_merge, when given, an object. All: timeout_seconds 1–120; any malformed _env: reference anywhere in the profile is flagged. Unknown keys pass through (forward-compatible).

Sending (Sender)¶

Function	Returns	Notes
`compose(p_profile, p_to, p_subject, p_body_text, p_body_html, p_cc, p_bcc, p_reply_to, p_sender, p_priority, p_debug, p_payload)`	bigint	Creates a draft. `p_profile` NULL/'' falls back to `pg_relay_notifier.default_profile`; a body-less message gets a single-space placeholder rather than raising. Validates the profile is active, recipients are present and shaped like addresses (webhook profiles skip the address-shape check — recipients are provider-native ids), priority 1–5. `p_payload` (webhook profiles only) is validated per provider by `_validate_payload_<provider>()`, and provider-specific compose gates apply (resend: sender + subject required, email-shaped recipients; telnyx: exactly one recipient, a sending number or `messaging_profile_id`; pagerduty: exactly one recipient, a summary for trigger events).
`attach(p_notification_id, p_filename, p_content bytea, p_content_type, p_inline)`	bigint	Draft-only; attachments freeze at dispatch. Enforces both `max_attach_mb` and `max_attach_number`, raising in the caller's transaction with the profile named.
`attach_text(p_notification_id, p_filename, p_content text, p_content_type, p_inline)`	bigint	Text convenience (stored UTF-8); default type `text/plain`.
`dispatch(p_notification_id, p_run_at, p_expire_at, p_channel, p_deduplicate)`	void	Enqueues (payload = the pk). `p_run_at` omitted: the profile's `delay_seconds` default applies if configured, else immediate. Raises if the profile is currently paused. Pre-checks the channel (registered, `action_type = 'notify'`, active). Commits with the caller's transaction.
`send(...)`	bigint	`compose` + `dispatch` in one call; all arguments of both.
`send_mail(p_profile, p_recipients, p_subject, p_message, p_mime_type, p_cc, p_bcc, p_sender, p_reply_to, p_priority)`	bigint	Oracle `UTL_MAIL.SEND` analogue.
`send_mail_attach_raw(..., p_attachment bytea, p_att_inline, p_att_mime_type, p_att_filename, ...)`	bigint	`UTL_MAIL.SEND_ATTACH_RAW` analogue.
`send_mail_attach_text(..., p_attachment text, ...)`	bigint	`UTL_MAIL.SEND_ATTACH_VARCHAR2` analogue.

Observability (Sender)¶

Function	Returns
`get_status(p_notification_id)`	TABLE — full delivery state including profile name, `provider_ref`, attempts, timestamps, `created_by`.
`list_notifications(p_status, p_since, p_limit)`	TABLE — newest first, optionally filtered.
`get_history(p_notification_id)`	TABLE — per-attempt outcomes, oldest first.
`get_trace(p_notification_id)`	TABLE — debug steps in order.

Maintenance and channels (Admin)¶

Function	Returns	Notes
`purge(p_older_than, p_statuses)`	integer	Deletes matching notifications older than the cutoff (attachments cascade; history and traces swept, including orphans). Default statuses: the four terminal ones. Refuses `pending`/`retry`.
`purge_debug(p_older_than)`	bigint	Trims debug traces independently (default 7 days).
`create_channel(p_channel, p_max_retries, p_concurrency_mode, p_notes)`	void	Wraps `pgrelay.register(...)` with `action_type = 'notify'`. `'channel'` mode serializes a mailbox.

Grants (Superuser)¶

Function	Confers
`grant_sender(p_role_name)`	Schema `USAGE` + sending and observability.
`grant_admin(p_role_name)`	Everything in `grant_sender` plus profiles, channels, maintenance.

Internal¶

_validate_profile(text, jsonb), _validate_env_refs(jsonb), _clean_addresses(text[], text, boolean), _split_addresses(text), _set_updated_at(), and the webhook adapter internals — _render_webhook_body(bigint), the per-provider _render_body_*/_classify_*/_validate_payload_* sets (slack, resend, telnyx, pagerduty) — implementation helpers, not granted, not part of any contract.