pgMail v1.4 (2017-11-16)
pgMail is a stored function written in TCL which takes 4 arguments of type ‘text’ (Who is it from, who is it to, subject, and body of message), contacts the email server via TCL sockets, and transmits your email (Now UTF-8 Compatible!).
Prerequisites
Before you can use pgMail, you must install the TCL/u procedural language. TCL/u is an UNRESTRICTED version of TCL that your database can use in its stored functions. Before you go nuts installing the unrestricted TCL procedural language in all of your databases, take into account that you must prepare adequate security precautions when adding the TCL/u language to your database! I will not be responsible for misconfigured servers allowing dangerous users to do bad things!
To install the TCL/u procedural language, you must have compiled (or used binary packages) and installed the TCL extensions of PostgreSQL. Once you are sure this has been completed, simply type the following at the unix shell prompt as a database administrator.
# createlang pltclu [YOUR DATABASE NAME]
In the place of [YOUR DATABASE NAME], put the name of the database to which you will be adding the stored procedure. If you want it to be added to all NEW databases, use template1 as your database name.
BEFORE ADDING THE PROCEDURE TO YOUR DATABASE YOU MUST DO THE FOLLOWING!!!
Replace the text <ENTER YOUR MAILSERVER HERE> with the fully qualified domain name for your mailserver. i.e., mail.server.com.
Replace the text <ENTER YOUR DATABASESERVER HERE> with the fully qualified domain name for your database server. i.e., db.server.com.
Once you have done the above, you are ready to go.
After this step, use the psql interface to add the pgMail function. Just copy the contents of the pgmail.sql file and paste it into your window. You may also load it directly from the command line by typing:
# psql -e [YOUR DATABASE NAME] < pgMail.sql
Once you have installed the stored function, simply call the procedure as follows.
select pgmail('Send From ','Send To ','Subject goes here','Plaintext message body here.');
select pgmail('Send From ','Send To ','Subject goes here','','HTML message body here.');
Or now, multipart MIME!
select pgmail('Send From ','Send To ', 'Subject goes here','Plaintext message body here.', 'HTML message body here.');
In both the “Send From” and “Send To” fields, you may include either only the email, or the email enclosed in <> with a plaintext name.
Testing Your Install
I have included an example for you to try. You MUST FIRST replace the string in the example.execute.sql script with your real email address, and install the plpgsql language just like you did the pltclu above. You can do that by entering a createlang [YOUR DATABASE NAME] plpgsql.
Once that is complete, first run the example.setup.sql. Then execute the example.execute.sql script. Provided everything is working well, you will see 2 emails in your mailbox. To remove this example, execute the example.cleanup.sql script.
SMTP Auth
pgMail does not support SMTP Auth. Most of the folks that use it either set up a local mailserver on the database server for local queueing and then use that setup for any relaying required (with auth). Or, alternatively, there is usually a special rule made in the the /etc/mail/access (or equivalent) file to allow relaying from that IP used by the database server. Obviously, the latter option doesn’t work with GMail.
Part of the reasoning behind this is that auth will be problematic in the transactional nature of pgMail for big jobs. The ideal solution would be to drop an EXIM server on the database server and have that handle any type of authentication as a smart relay server. Here is a link that has more info on how to set this up.