Skip to content

Instantly share code, notes, and snippets.

@dirkjanfaber
Last active August 29, 2015 14:11
Show Gist options
  • Save dirkjanfaber/ed566afabb06ffd3bf53 to your computer and use it in GitHub Desktop.
Save dirkjanfaber/ed566afabb06ffd3bf53 to your computer and use it in GitHub Desktop.
pgagent mail job
#!/bin/bash
export PGSERVICE=database
MAILTO=user@example.com
(
cat <<__EOT__
<html>
<body>
<pre>
__EOT__
(
cat <<__EOT__
\pset linestyle ascii
select 'hello world' as message, 42 as answer
__EOT__
) |\
psql -q -1 -v ON_ERROR_STOP=1 --pset pager=off
cat <<__EOT__
</pre>
</body>
</html>
__EOT__
) |\
mutt -e "set content_type=text/html" ${MAILTO}
exit $?
@dirkjanfaber
Copy link
Author

When you want to use pgagent for scheduling within PostgreSQL and want the output to be mailed to you, put the (edited) code into the Definition part of the job in pgAdmin.
The script uses html to format the mailed output.
Note that the script requires a functional pg_service.conf and mutt to be installed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment