Re: Best way to use trigger to email a report ?

> On May 8, 2020, at 09:26, David Gauthier <davegauthierpg(at)gmail(dot)com> wrote:
>
> psql (9.6.0, server 11.3) on linux
>
> Looking for ideas. I want a trigger to...
> 1) compose an html report based on DB content
> 2) email the report to a dist list (dl = value of a table column)

You probably *don't* want to actually send an email within a trigger; doing an operation that can block on an external service (DNS, SMTP) within a trigger is asking for hard-to-diagnose trouble. You probably don't even want to create the HTML; that's adding a lot of time to the operation that fires the trigger.

I'd probably set up the trigger to store the minimal data required to produce the HTML into a separate table, and then have a background job query the table, create the HTML, and mail out the report. If you don't want to periodically poll the table, you can use NOTIFY within the trigger to wake up a process that is waiting on NOTIFY.

--
-- Christophe Pettus
xof(at)thebuild(dot)com