orafce_mail

I wrote new Postgres's extension - orafce_mail. This extension enhancing an orafce about possibility to send mail from Postgres. I tested this extension against my gmail account, and it is work well. Just you need to generate special application's password, and that is all. This extension use library libcurl, and the features and possibilities of this extension is limitted by this library. It uses relativly new features of this library, so it cannot be linked with older versions of libcurl. The usage is simple:

You have to set configure variables:

set orafce_mail.smtp_server_url to 'smtps://smtp.gmail.com:465';
set orafce_mail.smtp_server_userpwd to 'pavel.stehule@gmail.com:yourgoogleapppassword';

After that you can send an mail without an attachment:

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ůň');

or mail with an attachment:

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
$$;

The implemented API is almost compatible with Oracle's package utl_mail and dbms_mail. This extension requires PostgreSQL 10 and higher (with procedures support).