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 folowing steps:
-
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(
'[email protected]',
array['[email protected]','[email protected]'],
array['[email protected]','[email protected]','[email protected]'],
array['[email protected]','[email protected]','[email protected]'],
'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(
'[email protected]',
array['[email protected]','[email protected]'],
array['[email protected]','[email protected]','[email protected]'],
array['[email protected]','[email protected]','[email protected]'],
'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> by the your email server config values.
If you plan to send emails using gmail make sure you allow less secure apps into your account
You can use this function to send emails with cc and bcc.