PL/Perl用Postgresql发送邮件 [英] PL/Perl send mail in Postgresql

问题描述

可能重复:
psql触发器发送电子邮件

Possible Duplicate:
psql trigger send email

我使用PL/Perl语言在Postgresql中发送邮件.数据集表upload_status更改为已发布时，它将邮件发送到数据集表中作者的电子邮件地址.并在信中包含该作者在数据集中的一些记录.数据集PK是标识符.

I using PL/Perl language to send mail in Postgresql. When dataset table upload_status change to published, and it will send mail to the author's email address in dataset table. And in letter contains some records in this author in dataset. The dataset PK is identifier.

喜欢 从[e-mail]发送到(dataset.email)@ mail.com

like send from [e-mail] to (dataset.email)@mail.com

亲爱的博士(数据集作者)

Dear Dr. (dataset.author)

您的......(数据集.产品)已经.......

your...... (dataset.product) have already .......

所以如何使用PL/Perl和触发函数编写函数.

so how to write the function using PL/Perl and trigger function.

谢谢， 我使用此方法 http://evilrouters.net/2008/02 /01/send-email-from-postgresql/

Thanks, I using this method http://evilrouters.net/2008/02/01/send-email-from-postgresql/

mydb=# CREATE OR REPLACE FUNCTION mydb_mytable_insert_send_mail_function()
mydb-# RETURNS "trigger" AS
mydb-# $BODY$
mydb$# use Mail::Sendmail;
mydb$#
mydb$# $message = "A new entry has been added to the 'mytable' table.\n\n";
mydb$# $message .= "The new name is: $_TD->{new}{name}\n\n";
mydb$#
mydb$# %mail = ( From => $_[0], To => $_[1], Subject => $_[2], Message => $message);
mydb$#
mydb$# sendmail(%mail) or die $Mail::Sendmail::error;
mydb$# return undef;
mydb$# $BODY$
mydb-# LANGUAGE 'plperlu' VOLATILE;

mydb=# CREATE TRIGGER mydb_mytable_insert_send_mail_trigger
mydb=# AFTER INSERT ON mytable
mydb=# FOR EACH ROW
mydb=# EXECUTE PROCEDURE mydb_mytable_insert_send_mail_function('[e-mail]',          '[e-mail]', 'subject here');

但显示发生了错误无法在@INC中找到Mail/Sendmail.pm(@INC包含:/usr/...

but it show an error has occurred Can't locate Mail/Sendmail.pm in @INC(@INC contains: /usr/...

推荐答案

只是因为您不能代表您应该这样做.有更好的方法可以做到这一点.不要直接从PL执行此操作.如果要忽略我的警告，请使用PL/PerlU并像编写其他任何电子邮件客户端一样编写它.您可以使用任何喜欢的CPAN模块，让您的生活更轻松.

Just because you can doesn't mean you should. There are better ways to do this. Don't do it directly from a PL. If you want to ignore my warnings, use PL/PerlU and write it like you would any other email client. You can use any CPAN modules you like that make your life easier.

不这样做的两个原因

1)如果您的交易中止/回滚怎么办?您已发送电子邮件，但未对数据库进行相应更改.您正在事务中执行非事务性的工作.

1) What if your transaction aborts/rolls back? You have sent the email but made no corresponding change to the db. You are doing non-transactional stuff inside a transaction.

2)如果您的电子邮件因等待响应而挂起，直到2分钟后出现tcp超时，该怎么办?您是否会忘记给客户发送电子邮件?中止交易(无法发送电子邮件，不能说我们已经发货了！)?

2) What if your email hangs waiting for a response until you get a tcp timeout after 2 min? Are you going to forget about emailing the customer? Abort the transaction (can't send email, can't say we have shipped the part!)?

这是一个坏的想法.不要这样感谢PostgreSQL遇到此错误，并将其移到另一个守护程序中.

This is a bad idea. Don't do it. Thank PostgreSQL for this error and move it out into another daemon.

更好的一种方法是使用LISTEN和NOTIFY以及队列表.然后，您可以创建一个像这样的表:

A much better approach is to use LISTEN and NOTIFY, and queue tables. You can then create a table like this:

CREATE TABLE email_queue (
    id serial not null unique,
    email_from text,
    email_to text not null,
    body text not null
); 

CREATE FUNCTION email_queue_trigger() RETURNS TRIGGER 
LANGUAGE PLPGSQL AS $F$
    BEGIN
        NOTIFY emails_waiting;
    END;
$F$;

然后将您的存储过程插入该表中.

Then have your stored procedure insert into that table.

然后，再创建一个客户端应用程序，该应用程序将在emails_waiting上侦听(sql语句LISTEN emails_waiting)，然后执行以下操作:

Then, have a second client app which LISTENs on the emails_waiting listens (sql statement LISTEN emails_waiting) and then does as follows:

1. 检查email_queue中是否有记录.如果不是，请执行3.
2. 读取数据，发送电子邮件，删除记录并提交.
3. 当队列为空时，它会睡x秒
4. 唤醒时，检查异步.通知(取决于客户端库，请检查文档).如果有，请转到1，否则，请转到3.

这使您的电子邮件可以排队等待发送交易，并自动传递到另一个应用程序，然后可以根据需要选择与MTA连接.

This allows your emails to be queued for sending in your transaction and for this to be automatically passed to another application which can then connect with the MTA if your choice.

第二个客户端应用可以使用您所知道的任何工具，以您选择的语言编写.它具有使所有网络事务都从事务中排除的优势，因此，如果您通过第二个SMTP服务器发送邮件，并且连接挂起，则整个数据库事务不会等待2分钟以使其超时并中止事务.因此，它对于将来的需求变更也更加安全.

That second client app can be written in the language of your choice, using whatever tools you know. It has the advantage of doing all network stuff out of the transaction, so if you are sending via a second SMTP server, and the connection hangs, your whole database transaction doesn't wait for 2 min for it to time out and abort the transaction. It is also thus safer against future changes in requirements.

这篇关于PL/Perl用Postgresql发送邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！