如何从PostgreSQL触发器发送电子邮件？

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

我使用pgsql设置触发器，当更新表数据集（将状态更改为已完成）时，它将使用数据集电子邮件值自动向电子邮件帐户发送电子邮件并将此电子邮件保存在服务器中

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

Pg版本为9.1，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;

5 个解决方案

#1

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

请参阅优秀的depesz文章和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.

直接从数据库发送电子邮件可能不是一个好主意。如果DNS解析速度很慢并且所有内容都会挂起30秒然后超时怎么办？如果您的邮件服务器抖动并需要5分钟接受邮件怎么办？您将在触发器中挂起数据库会话，直到您处于max_connections状态，突然间您无法执行任何操作，只需等待或开始手动取消事务。

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).

我建议你使用触发器NOTIFY一个LISTENING帮助脚本，该脚本保持永久运行并连接到DB（但不在事务中）。

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.

您的所有触发器都要将行插入队列表并发送NOTIFY。您的脚本获取NOTIFY消息，因为它已为其注册LISTEN，检查队列表，并完成剩下的工作。

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

你可以用任何方便的语言编写帮助程序;我通常使用Python和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.

该脚本可以根据在数据库中找到的信息发送电子邮件。您不必在PL / PgSQL中执行所有丑陋的文本格式化，您可以使用更强大的脚本语言将事物替换为模板，而只需在NOTIFY进入时从数据库中获取变量数据。

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.

如果您真的必须在数据库中执行此操作，请参阅PgMail。

#2

Use a local MTA (this gives you centralized SMTP config for multiple apps)
使用本地MTA（这为您提供了多个应用程序的集中SMTP配置）
Have the local MTA relay to your real MTA (this gives you async support, essentially)
将本地MTA中继到您真正的MTA（这实际上为您提供异步支持）
If windows, use blat SMTP command line client. Make sure the path to blat is in the PATH
如果是windows，请使用blat SMTP命令行客户端。确保blat的路径在PATH中
You should probably do this using Apache Camel or pgAgent, and not directly in a trigger
您可能应该使用Apache Camel或pgAgent执行此操作，而不是直接在触发器中执行此操作

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

如果是postgres超级用户，这将在Windows上运行。触发功能应该是SECURITY DEFINER。类似于Linux上的sendmail：

...

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

~60毫秒

#3

You can use plperlu to send mail.

您可以使用plperlu发送邮件。

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

此链接显示了如何在触发器上使用它的示例。

#4

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

您可以使用pgMail（如果您可以安装它）：

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

如果您按照brandolabs.com上的说明进行操作，则归结为

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

#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.

我同意@Craig Ringer的观点。您可以使用100行代码在Python中编写代码。我建议使用以下Python库：psycopg2，smtplib。根据您希望获得更改通知的频率，您可以运行cronjob（取决于您的工作环境）。这样，您可以将对数据库的多个更改聚合到一个电子邮件中，而不是每次发生更改时都发送通知。

#1