F.79. utl_mail — manage emails #
utl_mail is a Postgres Pro extension designed for managing emails, which includes commonly used email features, such as attachments, CC, and BCC. The functionality provided by this module overlaps substantially with the functionality of Oracle's UTL_MAIL package but larger amount of data can be sent as in Postgres Pro the text type is used for sending data, which can store up to 1GB.
F.79.1. Installation #
The utl_mail extension is a built-in extension included into Postgres Pro Enterprise. To enable utl_mail, create the extension using the following query:
CREATE EXTENSION utl_mail;
F.79.2. utl_mail Functions #
utl_mail provides functions for email management. Note that some of the parameters within functions below, namely host, port, timeout, username, and password, are required to establish connection with the SMTP server and authentication.
-
send(#hosttext,portint,timeoutint,usernametext,passwordtext,sendertext,recipientstext,messagetext,cctextdefault null,bcctextdefault null,subjecttextdefault null,mime_typetextdefault 'text/plain; charset=UTF-8',priorityintdefault 3,replytotextdefault null,starttlsbooldefault true) Packages an email into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients.
-
send_attach_bytea(#hosttext,portint,timeoutint,usernametext,passwordtext,sendertext,recipientstext,attachmentbytea,messagetext,cctextdefault null,bcctextdefault null,subjecttextdefault null,mime_typetextdefault 'text/plain; charset=UTF-8',priorityintdefault 3,att_inlinebooldefault true,att_mime_typetextdefault 'application/octet-stream',att_filenametextdefault null,replytotextdefault null,starttlsbooldefault true) Packages an email into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients with a
byteaattachment.The
attachmentspecifies thebyteaattachment. Theatt_inlinespecifies whether the attachment is viewable within the message body, default is true. Theatt_mime_typeis the mime type of the attachment, default isapplication/octet. Theatt_filenameis the string specifying the filename of the attachment, default is null.-
send_attach_text(#hosttext,portint,timeoutint,usernametext,passwordtext,sendertext,recipientstext,attachmenttext,messagetext,cctextdefault null,bcctextdefault null,subjecttextdefault null,mime_typetextdefault 'text/plain; charset=UTF-8',priorityintdefault 3,att_inlinebooldefault true,att_mime_typetextdefault 'text/plain; charset=UTF-8',att_filenametextdefault null,replytotextdefault null,starttlsbooldefault true) Packages an email into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients with a
textattachment.The
attachmentspecifies thetextattachment. Theatt_inlinespecifies whether the attachment is viewable within the message body, default is true. Theatt_mime_typeis the mime type of the attachment, default isapplication/octet. Theatt_filenameis the string specifying the filename of the attachment, default is null.
F.79.3. Example #
The following example demonstrates sending an email with an attachment using utl_mail.
CREATE TABLE pictures (pic bytea);
INSERT INTO pictures (pic) values (pg_read_binary_file('/home/postgres/slon.jpg'));
DO $$
DECLARE
picture bytea;
BEGIN
SELECT * INTO picture FROM pictures LIMIT 1;
CALL utl_mail.send_attach_bytea
(
host => ' smtp.mail.ru ',
port => 25,
timeout => 10,
username => ' username@mail.ru ',
password => <password>,
sender => 'Sender < sender@mail.ru >',
recipients => 'Recipient < recipient@mail.ru >',
message => 'Letter from pgpro_utl_mail!',
attachment => picture,
subject => 'utl_mail letter with picture',
att_filename => 'slon.jpg',
priority => 1
);
END$$;