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.
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;
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.
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.93. reply Parameters
| Parameter | Description |
|---|---|
code | 3-digit reply code |
text | Text message of the reply |
The connection type represents an SMTP connection.
Table G.94. 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. |
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.
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$$;