The utl_mail extension is provided with Postgres Pro Enterprise in a separate pre-built package pgpro-orautl-ent-16 (for the detailed installation instructions, see Chapter 17). To enable utl_mail, create the extension using the following query:

CREATE EXTENSION utl_mail;

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.

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.example.com',
            port => 25,
            timeout => 10,
            username => 'username@example.com',
            password => <password>,
            sender => 'Sender <sender@example.com>',
            recipients => 'Recipient <recipient@example.com>',
            message => 'Letter from pgpro_utl_mail!',
            attachment => picture,
            subject => 'utl_mail letter with picture',
            att_filename => 'slon.jpg',
            priority => 1
          );
END$$;