基于POSTGRES数据库新增行触发邮件发送的最简实现方案

方案一：触发器 + PL/Python 内置邮件发送 #

这种方式直接在数据库函数内处理邮件逻辑，无需依赖外部脚本。

1. 启用PL/Python扩展 #

首先确保PostgreSQL已安装并启用PL/Python3扩展：

CREATE EXTENSION IF NOT EXISTS plpython3u;

2. 创建触发函数 #

编写PL/Python函数，捕获新增行数据，当state为Arizona时发送邮件：

CREATE OR REPLACE FUNCTION send_arizona_user_email()
RETURNS TRIGGER AS $$
import smtplib
from email.mime.text import MIMEText

def send_notification(to_email):
    # 替换为你的SMTP配置
    smtp_server = 'smtp.example.com'
    smtp_port = 587
    sender_addr = 'alerts@yourdomain.com'
    sender_pass = 'your-smtp-password'

    subject = '新用户注册提醒（Arizona地区）'
    content = f"检测到来自Arizona的新用户，邮箱地址：{to_email}"

    msg = MIMEText(content)
    msg['Subject'] = subject
    msg['From'] = sender_addr
    msg['To'] = to_email

    try:
        with smtplib.SMTP(smtp_server, smtp_port) as server:
            server.starttls()
            server.login(sender_addr, sender_pass)
            server.send_message(msg)
    except Exception as e:
        plpy.error(f"邮件发送失败：{str(e)}")

# 获取新增行的字段值
new_state = TD['new']['state']
user_email = TD['new']['email']

if new_state == 'Arizona':
    send_notification(user_email)

return None
$$ LANGUAGE plpython3u;

3. 创建触发器 #

为用户表添加AFTER INSERT触发器，触发上述函数：

CREATE TRIGGER trigger_arizona_user_alert
AFTER INSERT ON your_user_table
FOR EACH ROW
EXECUTE FUNCTION send_arizona_user_email();

方案二：触发器 + 外部邮件脚本 #

如果更倾向于将邮件逻辑与数据库解耦，可通过触发器调用外部脚本处理发送。

1. 启用必要扩展 #

需要启用dblink和pg_cmdshell来执行外部命令：

CREATE EXTENSION IF NOT EXISTS dblink;
CREATE EXTENSION IF NOT EXISTS pg_cmdshell;

2. 创建触发函数 #

编写PL/pgSQL函数，当满足条件时调用外部脚本：

CREATE OR REPLACE FUNCTION trigger_call_email_script()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.state = 'Arizona' THEN
        -- 调用外部Python脚本，传递用户邮箱作为参数
        PERFORM dblink_exec('dbname=' || current_database(),
            'SELECT pg_cmdshell(''python3 /opt/scripts/send_user_alert.py ' || quote_literal(NEW.email) || ''');');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 编写外部邮件脚本 #

创建/opt/scripts/send_user_alert.py脚本，内容如下：

import sys
import smtplib
from email.mime.text import MIMEText

def send_email(to_email):
    smtp_server = 'smtp.example.com'
    smtp_port = 587
    sender_addr = 'alerts@yourdomain.com'
    sender_pass = 'your-smtp-password'

    subject = '新用户注册提醒（Arizona地区）'
    content = f"检测到来自Arizona的新用户，邮箱地址：{to_email}"

    msg = MIMEText(content)
    msg['Subject'] = subject
    msg['From'] = sender_addr
    msg['To'] = to_email

    try:
        with smtplib.SMTP(smtp_server, smtp_port) as server:
            server.starttls()
            server.login(sender_addr, sender_pass)
            server.send_message(msg)
    except Exception as e:
        print(f"发送失败：{str(e)}", file=sys.stderr)
        sys.exit(1)

if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("需传入用户邮箱作为参数", file=sys.stderr)
        sys.exit(1)
    send_email(sys.argv[1])

4. 创建触发器 #

CREATE TRIGGER trigger_external_email_script
AFTER INSERT ON your_user_table
FOR EACH ROW
EXECUTE FUNCTION trigger_call_email_script();

关键注意事项 #

• 权限配置：确保PostgreSQL运行用户（通常是postgres）拥有脚本的执行权限，且SMTP配置的账号有发送权限。
• 异步优化：生产环境建议将邮件任务放入消息队列（如Redis、RabbitMQ），避免数据库因邮件发送阻塞；可修改触发器将任务写入队列，由独立服务处理发送。
• 错误处理：在函数或脚本中添加错误日志，比如写入数据库日志表，方便排查发送失败问题。
• 邮件服务选择：避免使用个人邮箱SMTP，推荐使用SendGrid、Mailgun等专业邮件服务，提升送达率并避免被标记为垃圾邮件。

内容的提问来源于stack exchange，提问作者liatkatz