digoal
2021-08-23
PostgreSQL , utl_mail , dbms_mail
https://github.com/okbob/orafce_mail
依赖curl包, 支持附件, 转发时, 建议使用本地smtp server, 速度更快.
有什么用呢? 结合触发器和dblink异步调用, 当发生某些事件时提供邮件告警.
或者使用pg_cron定时任务, 定时发送统计报告或健康报告等.
This is implementation of Oracle's API of packages utl_mail, DBMS_MAIL
It doesn't ensure full compatibility, but should to decrease a work necessary for
successful migration.
These functions can be used by user that is member of role orafce_mail. For setting of
orafce_mail.smtp_server_url the user should be member of role orafce_mail_config_url.
For setting of orafce_mail.smtp_server_userpwd the user should be member of role
orafce_mail_config_userpwd.
set orafce_mail.smtp_server_url to 'smtps://smtp.gmail.com:465';
set orafce_mail.smtp_server_userpwd to 'pavel.stehule@gmail.com:yourgoogleapppassword';
call utl_mail.send(sender => 'pavel.stehule@gmail.com',
recipients => 'pavel.stehule@gmail.com',
subject => 'ahoj, nazdar, žlutý kůň',
message => e'test, \nžlutý kůň');
do $$
declare
myimage bytea = (select img from foo limit 1);
begin
call utl_mail.send_attach_raw(sender => 'pavel.stehule@gmail.com',
recipients => 'pavel.stehule@gmail.com',
subject => 'mail with picture',
message => 'I am sending some picture',
attachment => myimage,
att_mime_type => 'image/png',
att_filename => 'screenshot.png');
end
$$;
This extensions uses curl library.
An extension Orafce should be installed before
Sending to remote smtp server is pretty slow. This is not an issue of orafce_mail
or curl library. So don't try to send mails from performance critical processes.
Use some buffer table, and send mails from another process started by cron or some
scheduler application, or use local smtp server. Postgres has great LISTEN/NOTIFY
mechanism.
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
