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

David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:

> On Fri, May 8, 2020 at 9:26 AM 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)
>>
>> If this will involve hybrid coding, I prefer PL/perl. The linux env has
>> both "mail" and "mutt" (if this is of any help).
>>
>> The idea is to send a report to the list when all the data has been
>> collected for a particular job and the final status of the job is updated
>> as a col of a rec of a certain table. Probably a post update trigger.
>>
>
> I'd probably limit the trigger to checking for the completion of the data
> collection and inserting a record into a "email job" table. Then I'd have
> cron on a linux machine periodically run a script that queries the "email
> job" table for work, perform the work, and then either flag the work as
> done or remove the job record.
>

Yep, exactly how I would do it as well. Personally, triggers are my last
choice. In some situations, they are the right choice and when you do
need them, keep them as small and simple as possible.

Others have mentioned the issues of using external calls inside a
trigger. In addition to the problem with waiting on external processes
to complete, you also have all the messy external work things to take
care of (like network down, server unavailable, etc).

The solution I've used in the past is to have procedures in the database
which generate the email report and insert that data into an email
table. I then have a shell level script (could be perl, javascript,
java, whatever) which looks in this table periodically and if it finds a
report which has not been sent, extract it, optionally format it and
send it. On successful completion, set a 'sent' flag on the report
record in the DB (or just delete it - I prefer to set a flag so that if
something failed unexpectedly, you still have the report).

There are ways you can trigger periodic activity in the database, but to
be honest, CRON is easy and reliable and avoids needing to add
additional extensions etc to the DB. Turning off the report, changing
the time etc, is a simple crontab edit.

--
Tim Cross