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. `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.

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)`	bigint	Validates the profile block, listing every issue in one error; secret-bearing keys (`password`/`client_secret`) must be `_env:VAR_NAME` references. Case-insensitive unique names. Attachment limits default from `profile_defaults` for the transport; explicit arguments win.
`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)`	void	NULL keeps the current value; empty string clears `p_send_from`/`p_reply_to`. `p_profile`, when given, replaces the whole connection block (secrets included) and is re-validated. Transport 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).
`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 (defaulted from username); auth ⇒ username + password (must be an _env: reference). M365: tenant_id, client_id, sender, and client_secret (must be an _env: reference) required; save_to_sent_items boolean. Both: 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)`	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, priority 1–5.
`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). 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, text), _clean_addresses(text[], text), _split_addresses(text), _set_updated_at() — implementation helpers, not granted, not part of any contract.