Your First Profile and Send

If you're a developer working against a database where a DBA has already set up a profile, skip to Sending your first email — you just need a profile name.

Setting up a profile for local development

For a local/dev database where you're both the DBA and the sender:

SELECT pgrelay_notifier.create_channel('notifications');

SELECT pgrelay_notifier.create_profile('mailer', 'smtp',
    '{"host": "smtp.example.com", "username": "app",
      "password": "_env:SMTP_PASSWORD", "from": "[email protected]"}'::jsonb,
    p_channel := 'notifications');

SELECT pgrelay_notifier.grant_sender('app_role');

password is an _env:VAR_NAME reference, not a literal value — the pg_relay Processor resolves it from its own host environment at send time. See the DBA Guide for the full profile-setup walkthrough, including Microsoft 365.

Sending your first email

SELECT pgrelay_notifier.send(
    p_profile   => 'mailer',
    p_to        => ARRAY['[email protected]'],
    p_subject   => 'Your order has shipped',
    p_body_text => 'Order 1234 shipped today.',
    p_body_html => '<p>Order <b>1234</b> shipped today.</p>'
);
-- → returns the notification_id

With both bodies set, SMTP recipients get a proper multipart/alternative; Microsoft 365 sends the HTML. The call commits with your transaction — roll back, and no email happens.

The full argument list

compose() (and send(), which wraps it) takes:

Argument	Meaning
p_profile	The profile name. NULL/'' falls back to the database's default profile, if your DBA configured one — see Profile Defaults.
p_to	Recipients, text[]. At least one required.
p_subject	Subject line (Q-encoded automatically for non-ASCII).
p_body_text / p_body_html	Neither is required on its own — a message with no body at all is accepted (a single-space placeholder is stored). Set both for a proper multipart message.
p_cc / p_bcc	Copy recipients, text[]. bcc is envelope-only — it never appears in headers.
p_reply_to	Reply-To header. Omitted → the profile's default, if configured, else none.
p_sender	Per-message From override. Omitted → the profile's default, if configured, else the profile's own connection address.
p_priority	1 (highest) – 5 (lowest). See Core Concepts.
p_debug	true turns on a live delivery trace for this one message — see Debug Tracing.

send() additionally takes:

Argument	Meaning
p_run_at	Defer delivery until this timestamp. Default: now.
p_expire_at	Discard, unsent, if not delivered by this timestamp.
p_channel	Override the profile's default channel for this one send.
p_deduplicate	Suppress the enqueue if an identical pending notification already exists on the channel.

Deferred and expiring sends

-- send at 9am, give up if still undelivered by 5pm
SELECT pgrelay_notifier.send('mailer', ARRAY['[email protected]'], 'Reminder', 'b',
    p_run_at    => date_trunc('day', now()) + interval '9 hours',
    p_expire_at => date_trunc('day', now()) + interval '17 hours');

---