Postgres Pro Enterprise : Documentation: 17: G.9. utl_smtp — send emails over SMTP : Postgres Professional

G.9. utl_smtp — send emails over SMTP
Prev	Up	Appendix G. Postgres Pro Modules and Extensions Shipped as Individual Packages	Home	Next

G.9. utl_smtp — send emails over SMTP #

G.9.1. Installation
G.9.2. Usage
G.9.3. Data Types
G.9.4. utl_smtp Functions
G.9.5. Examples

utl_smtp is a Postgres Pro extension designed for sending emails over SMTP from PL/pgSQL. The functionality provided by this module overlaps substantially with the functionality of Oracle's UTL_SMTP package.

G.9.1. Installation #

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

CREATE EXTENSION utl_smtp;

G.9.2. Usage #

To send an email using utl_smtp, the functions must be called in a specific order:

First, open connection by calling the open_connection function.
Then send HELO/EHLO command to the SMTP server using helo/ ehlo respectively.
Send sender and recipient addresses using mail and rcpt functions.
Call the open_data function to start data sending process by sending the DATA command to the SMTP server.
After that, write_data and write_raw_data can be called repeatedly to send the actual data.
The data sending process is terminated by calling close_data.
Once open_data is called, the only functions that can be called are write_data, write_raw_data, or close_data. Other calls result in an error being raised.
Alternatively, data sending process can be streamlined with one call of data.
After calling close_data or data, the email is sent — call quit to end the connection.

G.9.3. Data Types #

The utl_smtp extension provides the following data types:

The reply type is used to represent an SMTP reply line. Each SMTP reply line consists of a reply code followed by a text message. While a single reply line is expected for most SMTP commands, some SMTP commands expect multiple reply lines.
Table G.97. reply Parameters
Parameter Description
code 3-digit reply code
text Text message of the reply

Parameter	Description
`code`	3-digit reply code
`text`	Text message of the reply

The connection type represents an SMTP connection.

Table G.98. connection Parameters

Parameter	Description
`host`	Name or IP address of the remote host when connection is established.
`port`	Port number of the remote SMTP server connected.
`tx_timeout`	Time in seconds that utl_smtp waits before timing out in a read or write operation in this connection. The timeout of server connection is always 60 seconds and cannot be configured.
`private_socket`	This parameter is used internally by Postgres Pro and should not be modified manually.

G.9.4. utl_smtp Functions #

utl_smtp provides functions for sending emails over SMTP. Note that the functions that are supposed to return multiple reply lines return the reply array.

auth(c connection, username text, password text, schemes text default 'PLAIN') returns reply #: Sends the AUTH command to authenticate to the SMTP server. Currently only the PLAIN authentication scheme is supported.
close_all_connections() returns void #: Closes all SMTP connections and releases all associated resources.
close_connection(c connection) returns void #: Closes the specified SMTP connection. This function can be called when sending data to the server before close_data. In this case, the next call to a function with this connection will raise an exception.
close_data(c connection) returns reply #: Ends the e-mail message by sending the sequence <CR><LF>.<CR><LF> (a single period at the beginning of the line).
command(c connection, cmd text, arg text default null) returns reply #: Sends an arbitrary SMTP command and can return a single reply line.
command_replies(c connection, cmd text, arg text default null) returns reply[] #: Sends an arbitrary SMTP command and can return multiple reply lines.
data(c connection, body text) returns reply #: Specifies the body of an email. It is essentially a sequence of calls: open_data, write_data, and close_data.
ehlo(c connection, domain text) returns reply #: Performs the initial handshake with SMTP server using the EHLO command. The server returns a part of its configuration.
helo(c connection, domain text) returns reply #: Performs the initial handshake with SMTP server using the HELO command.
help(c connection, command text default null) returns reply #: Sends the HELP command. This command might not be implemented on all SMTP servers.
last_reply(c connection) returns reply #: Returns the last reply of the SMTP server.
mail(c connection, sender text, parameters text default null) returns reply #: Initiates an email transaction with the server by sending the MAIL command with the sender address.
noop(c connection) returns reply #: Issues the NOOP command. This function is mainly used to check the connection.
open_connection(host text, port int default 25, tx_timeout int default null, secure_connection_before_smtp bool default false, verify_peer bool default true) returns connection #: Opens a connection to an SMTP server. The secure_connection_before_smtp parameter specifies if the TLS connection is established before the SMTP connection (essentially, if this parameter is true, the connection will be using SMTPS instead of SMTP). The verify_peer parameter specifies if the certificates are validated when establishing the TLS connection.
open_data(c connection) returns reply #: Sends the DATA command after which you can use write_data and write_raw_data to write a portion of the email.
quit(c connection) returns reply #: Terminates an SMTP session and disconnects from the server.
rcpt(c connection, recipient text, parameters text default null) returns reply #: Specifies the recipient of an email. The message transaction must have been started by a prior call to MAIL, and the connection to the mail server must have been opened and initialized by prior calls to open_connection and helo or ehlo respectively.
rset(c connection) returns reply #: Terminates the current mail transaction. The client can call rset at any time after the connection to the SMTP server has been opened by means of open_connection until data or open_data is called.
set_reply_error_check(c connection, enable bool) returns void #: Determines function behavior. If the enable parameter is set to true, which is the default, any error on the SMTP server raises an exception. If it is set to false, the user is responsible for analyzing the results returned by the functions to determine the error.
starttls(c connection, verify_peer bool default true) returns reply #: Sends the STARTTLS command to secure the SMTP connection using TLS.
vrfy(c connection, recipient text) returns reply #: Sends the VRFY command to verify the validity of the destination email. This command might not be implemented on all SMTP servers, and it may not return the correct information so it is not recommended to use it.
write_data(c connection, data text) returns void #: Sends a portion of the text of the message, including headers, to the SMTP server. A repeated call to write_data appends data to the message.
write_raw_data(c connection, data text) returns void #: Sends a portion of the text of the message, including headers, to the SMTP server. A repeated call to write_data appends data to the message. The same as write_data.

G.9.5. Examples #

The following example demonstrates sending an email without attachment using utl_smtp.

DO $$
DECLARE
    conn utl_smtp.connection;
BEGIN
    conn := utl_smtp.open_connection('smtp.mail.ru', 25, 10);
    perform utl_smtp.ehlo(conn, 'localhost');
    perform utl_smtp.starttls(conn);
    perform utl_smtp.ehlo(conn, 'localhost');
    perform utl_smtp.auth(conn, 'test_email@example.com', 'super-secret-password');
    perform utl_smtp.mail(conn, 'sender@example.com');
    perform utl_smtp.rcpt(conn, 'recipient@example.com');
    perform utl_smtp.open_data(conn);
    perform utl_smtp.write_data(conn, E'Content-Type: multipart/mixed; boundary=------------------------6f48b7d5ded0c5fc\n');
    perform utl_smtp.write_data(conn, E'Mime-Version: 1.0\n');
    perform utl_smtp.write_data(conn, E'From: Sender <sender@example.com>\n');
    perform utl_smtp.write_data(conn, E'To: Recipient <recipient@example.com>\n');
    perform utl_smtp.write_data(conn, E'Subject: mail from utl_smtp\n');
    perform utl_smtp.write_data(conn, E'--------------------------6f48b7d5ded0c5fc\n');
    perform utl_smtp.write_data(conn, E'Content-Type: text/plain; charset=\"UTF-8\"\n');
    perform utl_smtp.write_data(conn, E'Content-Transfer-Encoding: 8bit\n\n');
    perform utl_smtp.write_data(conn, E'This is body from inside Postgres Pro\n');
    perform utl_smtp.write_data(conn, E'Sent using utl_smtp\n');
    perform utl_smtp.write_data(conn, E'\n--------------------------6f48b7d5ded0c5fc--\n');
    perform utl_smtp.close_data(conn);
    perform utl_smtp.quit(conn);
END$$;

The following example demonstrates exception handling with utl_smtp.

DO $$
DECLARE
    ...
    r utl_smtp.reply;
BEGIN
    ...
    some utl_smtp funcion calls
    ...
exception
    when others then
        r = utl_smtp.last_reply(conn);
        if r.code >= 500 then
            raise notice 'caught permanent error';
        elsif r.code >= 400 then
            raise notice 'caught transient error';
        else
            raise notice 'some other error';
        end if;
END$$;

Prev	Up	Next
G.8. utl_mail — manage emails	Home	G.10. TDE — enables page level encryption

epub pdf