Postgres Pro Enterprise : Documentation: 16: F.83. utl_mail — manage emails : Postgres Professional

F.83. utl_mail — manage emails
Prev	Up	Appendix F. Additional Supplied Modules and Extensions	Home	Next

F.83. utl_mail — manage emails #

F.83.1. Installation
F.83.2. utl_mail Functions
F.83.3. Example

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.83.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.83.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(host text, port int, timeout int, username text, password text, sender text, recipients text, message text, cc text default null, bcc text default null, subject text default null, mime_type text default 'text/plain; charset=UTF-8', priority int default 3, replyto text default null, starttls bool default 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(host text, port int, timeout int, username text, password text, sender text, recipients text, attachment bytea, message text, cc text default null, bcc text default null, subject text default null, mime_type text default 'text/plain; charset=UTF-8', priority int default 3, att_inline bool default true, att_mime_type text default 'application/octet-stream', att_filename text default null, replyto text default null, starttls bool default 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 bytea attachment.

The attachment specifies the bytea attachment. The att_inline specifies whether the attachment is viewable within the message body, default is true. The att_mime_type is the mime type of the attachment, default is application/octet. The att_filename is the string specifying the filename of the attachment, default is null.

send_attach_text(host text, port int, timeout int, username text, password text, sender text, recipients text, attachment text, message text, cc text default null, bcc text default null, subject text default null, mime_type text default 'text/plain; charset=UTF-8', priority int default 3, att_inline bool default true, att_mime_type text default 'text/plain; charset=UTF-8', att_filename text default null, replyto text default null, starttls bool default 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 text attachment.

The attachment specifies the text attachment. The att_inline specifies whether the attachment is viewable within the message body, default is true. The att_mime_type is the mime type of the attachment, default is application/octet. The att_filename is the string specifying the filename of the attachment, default is null.

F.83.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$$;

Prev	Up	Next
F.82. utl_http — access data on the Internet over the HTTP protocol	Home	F.84. utl_smtp — send emails over SMTP

epub pdf