This is a simple function in plpython3u (Python3) that sends an email from inside a PostgreSQL database.
In order to use this function please run the following steps:
-
Make sure the plpython3u extension is available in your database. If needed, enable it with:
CREATE EXTENSION plpython3u;
-
Create py_pgmail function by running py_pgmail.sql
-
After the function is created you can simply call the function as:
select py_pgmail('sentFromEmail',array['destination emails'],array['cc'],array['bcc'],'Subject','<USERNAME>','<PASSWORD>','Text message','HTML message','attachment.csv','/foo/bar/','<MAIL.MYSERVER.COM:PORT>')WARNING: You can send a message in plain text or in HTML. If you provide both plain text and HTML then only the HTML will be sent!
For example:
-------------------
-- HTML message --
-----------------
select py_pgmail(
'sentFromEmail@whatever.com',
array['dest@email.com','dest2@email.com'],
array['cc@email.com','cc2@email.com','cc3@email.com'],
array['bcc@email.com','bcc2@email.com','bcc3@email.com'],
'My subject',
'<USERNAME>',
'<PASSWORD>',
'Text message! This is an email sent from a database!',
'<!DOCTYPE html>
<html>
<body>
<p>html message! This is an email sent from a database!</p>
</body>
</html>',
'attachment.csv',
'/foo/bar/',
'smtp.gmail.com:587');
-------------------------
-- Plain text message --
-----------------------
select py_pgmail(
'sentFromEmail@whatever.com',
array['dest@email.com','dest2@email.com'],
array['cc@email.com','cc2@email.com','cc3@email.com'],
array['bcc@email.com','bcc2@email.com','bcc3@email.com'],
'My subject',
'<USERNAME>',
'<PASSWORD>',
'Text message! This is an email sent from a database!',
'',
'attachment.csv',
'/foo/bar/',
'smtp.gmail.com:587');Make sure you replace <USERNAME> <PASSWORD> and <MAIL.MYSERVER.COM:PORT> with your email server config values.
If you plan to send emails using gmail note that Google no longer supports "less secure apps". Instead, enable 2-Step Verification on your Google account and create an app password, then use that app password as <PASSWORD>.
You can use this function to send emails with cc and bcc.