How can I send email from PostgreSQL trigger?

Question

I using pgsql to set a trigger, when update the table dataset(change the status to Finished) it will automatic send a email to the email account using dataset email value and save this email in server

but i don't know how to write in trigger function to send email, and send email in server. Thank you in advance

Pg version is 9.1, and CentOS 5.8

CREATE OR REPLACE FUNCTION sss()
RETURNS trigger AS
$BODY$begin
if(NEW.publisher== 'aaaa')
then
//send email and save to server 192.168.171.64
end if;
return NEW;
end

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION sss()
OWNER TO postgres;
GRANT EXECUTE ON FUNCTION sss() TO postgres;

Answer 1

See the excellent-as-usual depesz article, and pg-message-queue.

Sending email directly from the database may not be a great idea. What if DNS resolution is slow and everything hangs for 30 seconds then times out? What if your mail server is having a wobbly and takes 5 minutes to accept messages? You'll get database sessions hung up in your trigger until you're at max_connections and suddenly you can't do anything but wait or start manually cancelling transactions.

What I'd recommend is having your trigger NOTIFY a LISTENing helper script that remains permanently running and connected to the DB (but not in a transaction).

All your trigger has to do is INSERT a row into a queue table and send a NOTIFY. Your script gets the NOTIFY message because it has registered to LISTEN for it, examines the queue table, and does the rest.

You can write the helper program in whatever language is convenient; I usually use Python with psycopg2.

That script can send the email based on information it finds in the database. You don't have to do all the ugly text formatting in PL/PgSQL, you can substitute things into a template in a more powerful scripting language instead, and just fetch the variable data from the database when a NOTIFY comes in.

With this approach your helper can send each message and only then remove the info from the queue table. That way if there are transient problems with your mail system that causes sending to fail, you haven't lost the info and can continue to attempt to send it until you succeed.

If you really must do this in the database, see PgMail.

Answer 2

1. Use a local MTA (this gives you centralized SMTP config for multiple apps)
2. Have the local MTA relay to your real MTA (this gives you async support, essentially)
3. If windows, use blat SMTP command line client. Make sure the path to blat is in the PATH
4. You should probably do this using Apache Camel or pgAgent, and not directly in a trigger

This will work on Windows if postgres superuser. Trigger function should be SECURITY DEFINER. Similar for sendmail on Linux:

...

copy 
  ( select 'my email body' ) 
to program 
  'blat -to to@example.com -from from@example.com -subject "My Subject" -server localhost:25' 
with (
  format text
);

...

~ 60 ms

Answer 3

You can use plperlu to send mail.

This link shows an example of how to use it on a trigger.

Answer 4

You have the possibility to use pgMail (if you are allowed to install it):

If you follow the instructions on brandolabs.com it comes down to

pgmail('Send From ','Send To ','Subject goes here','Message body here.')

Answer 5

I agree with @Craig Ringer. You could code something in Python under 100 lines of code. I would recommend using the following Python libraries: psycopg2, smtplib. Depending on how often you would like to get notified of the changes, you could run a cronjob (depending on your working environment). That way you can aggregate multiple changes to the database into a single email rather than sending a notification every time a change takes place.

Answer 6

If you use TypeScript/Node, then use the battle-tested graphile-worker codebase to communicate from Postgres to Node. Then you can easily use your standard email logic in Node to send a email.