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

Question

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

但我不知道如何编写触发器函数来发送电子邮件，并在服务器中发送电子邮件。谢谢您提前。

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;

- AntiGMO

---

当询问关于Pg的问题时，解释一下你的设置情况特别是你的Pg版本会非常有帮助。不同的版本具有不同的功能，因此版本可能会影响答案。 - Craig Ringer

Answer 1

请参阅出色的depesz文章和pg-message-queue。

直接从数据库发送电子邮件可能不是一个好主意。如果DNS解析缓慢，而一切都卡在那里30秒钟然后超时怎么办？如果您的邮件服务器出现问题并且需要5分钟才能接受邮件怎么办？触发器中将有数据库会话挂起，直到达到max_connections，突然之间你什么也做不了，只能等待或手动取消事务。

我建议的是，让您的触发器NOTIFY一个保持永久运行并连接到数据库（但不在事务中）的LISTENing辅助脚本。

您的触发器只需将一行插入队列表并发送NOTIFY即可。您的脚本获取NOTIFY消息，因为它已经注册以便LISTEN它，检查队列表并完成剩下的工作。

您可以使用任何方便的语言来编写辅助程序；我通常使用带有psycopg2的Python。

该脚本可以基于找到的数据库中的信息发送电子邮件。您不必在PL/PgSQL中进行所有丑陋的文本格式化，而是可以将事物插入更强大的脚本语言模板中，并在NOTIFY进来时从数据库获取变量数据。

使用这种方法，您的辅助程序可以发送每个消息，然后再从队列表中删除信息。因此，如果您的邮件系统出现短暂问题导致发送失败，您并没有丢失该信息，可以继续尝试发送它直到成功为止。如果您确实必须在数据库中进行此操作，请参考PgMail。

- Craig Ringer

---

@JesseSiu 你似乎从一个小时前在另一个答案中发布的评论中复制并粘贴了这个评论。这个答案与PL/Perl无关，我不建议你使用它，原因已在我的答案中解释。请使用在数据库之外运行的脚本发送邮件。 - Craig Ringer

@JesseSiu 如果您不理解上面的建议，或者这对您不起作用，请随时解释或要求澄清，我很乐意帮助。我只是认为您采取了错误的方法。 - Craig Ringer

我实际上是专门编写了pg-message-queue，以便它成为这个问题的答案。 - Chris Travers

Answer 2

1. 使用本地MTA（这为多个应用程序提供了集中的SMTP配置）
2. 让本地MTA转发到您的真实MTA（这基本上为您提供了异步支持）
3. 如果是Windows，请使用blat SMTP命令行客户端。确保blat的路径在PATH中
4. 最好使用Apache Camel或pgAgent来完成此操作，而不是直接在触发器中完成

如果postgres超级用户，则此方法适用于Windows。触发器函数应该是SECURITY DEFINER。对于Linux上的sendmail也是类似的。

...

copy 
  ( select 'my email body' ) 
to program 
  'blat -to [e-mail] -from [e-mail] -subject "My Subject" -server localhost:25' 
with (
  format text
);

...

~ 60 毫秒

- Neil McGuigan

---

这正是我正在寻找的解决方案，可惜“编程”只在PostgreSQL 9.3之后才可用。 - Thorsten Schöning

Answer 3

您可以使用plperlu发送邮件。

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

- Diego

---

谢谢，但是语言是plpgsql，当我输入use Mail::Sendmail;时，它显示语法错误。 - AntiGMO

@JesseSiu 在使用之前，你必须以超级用户身份执行CREATE LANGUAGE plperlu;命令来创建plperlu语言，并使用LANGUAGE 'plperlu';来指定使用该语言。 - Craig Ringer

当我输入 createlang plperlu mydb 时，它会显示无法打开扩展控制文件。在扩展文件夹中，它不包含 plperlu.control 文件？我该怎么办？ - AntiGMO

@JesseSiu 这完全取决于您如何安装PostgreSQL。两天前我要求您添加Pg版本，但您仍未更新答案。也许您应该这样做，并解释一下您所在的操作系统以及如何安装Pg？一旦有了这些信息，就可能会有人能够帮助您。 - Craig Ringer

抱歉，我忘记添加信息了。Pg版本为9.1，CentOS 5.8。 - AntiGMO

嗨，我正在使用您的方法，但出现了错误。在@INC中找不到Mail/Sendmail.pm（@INC包含：/usr/...）。您能看一下这个链接吗？http://stackoverflow.com/questions/12243335/pl-perl-send-mail-in-postgresql - AntiGMO

Answer 4

如果您被允许安装，您可以使用pgMail：

如果您按照brandolabs.com上的说明操作，就可以完成。

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

- Eggi

---

我可以不安装pgmail而使用其他方式吗？ - AntiGMO

你可以使用这个（安装程序）或者使用下面帖子中的plperlu程序。 - Eggi

Answer 5

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

- Adrian Aksan

Answer 6

如果您使用TypeScript/Node，则可以使用经过实战检验的graphile-worker代码库来从Postgres通信到Node。然后，您可以轻松地在Node中使用标准的电子邮件逻辑来发送电子邮件。

- pir