如何从 PostgreSQL 触发器发送电子邮件? [英] How can I send email from PostgreSQL trigger?

问题描述

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

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 版本是 9.1，CentOS 5.8

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;

推荐答案

参见 优秀的 depesz 文章 和 pg-message-queue>.

直接从数据库发送电子邮件可能不是一个好主意.如果 DNS 解析很慢并且一切都挂了 30 秒然后超时怎么办?如果您的邮件服务器出现问题并且需要 5 分钟 来接收邮件怎么办?您将在触发器中挂起数据库会话，直到您处于 max_connections 处，然后突然间您只能等待或开始手动取消事务.

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.

我建议您使用触发器 NOTIFY 保持永久运行的 LISTENing 帮助脚本并连接到数据库(但不在事务中).

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

您的触发器所要做的就是将一行INSERT 插入队列表并发送一个NOTIFY.您的脚本会收到 NOTIFY 消息，因为它已经为它注册了 LISTEN，检查了队列表，并完成了剩下的工作.

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.

你可以用任何方便的语言编写帮助程序；我通常将 Python 与 psycopg2 一起使用.

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

该脚本可以根据它在数据库中找到的信息发送电子邮件.你不必在 PL/PgSQL 中做所有丑陋的文本格式，你可以用更强大的脚本语言将东西替换到模板中，只需在 NOTIFY 进来了.

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.

如果您确实必须在数据库中执行此操作，请参阅PgMail.

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

这篇关于如何从 PostgreSQL 触发器发送电子邮件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！