utl_http is a
Postgres Pro extension that allows accessing
data on the Internet over the HTTP protocol (HTTP/1.0 and HTTP/1.1) by
invoking HTTP callouts from SQL and PL/pgSQL.
The functionality provided by this module overlaps substantially
with the functionality of Oracle's
UTL_HTTP package.
With utl_http, you can write programs
that communicate with HTTP servers. utl_http
also contains functions that can be used in SQL queries.
The extension supports HTTP over SSL, also known as HTTPS. The supported methods are
GET, POST PUT,
UPLOAD, PATCH, HEAD,
OPTIONS, DELETE, TRACE
(see https://datatracker.ietf.org/doc/html/rfc9110#name-methods),
as well as any custom HTTP-methods.
utl_http is typically used as follows:
A request is created by
begin_request.
Request parameters are set, for more information see Section G.8.3.3.
The response is processed by
get_response.
The obtained response is manipulated using procedures from Section G.8.3.5.
The utl_http extension is provided with
Postgres Pro Enterprise in a separate pre-built package
pgpro-orautl-ent-18
(for the detailed installation instructions,
see Chapter 17). To enable
utl_http, create the extension using the following
query:
CREATE EXTENSION utl_http;
For utl_http to work with SSL, a libcurl
library with OpenSSL support is required. E.g., libcurl4-openssl-dev
for Ubuntu.
The utl_http extension provides several data types:
req represents an HTTP request.
CREATE TYPE req AS ( url varchar(32767), method varchar(64), http_version varchar(64) );
Table G.98. req Parameters
| Parameter | Description |
|---|---|
url |
The URL of the HTTP request. It is set after the request
is created by
begin_request.
|
method |
The method to be performed on the resource identified by the URL.
It is set after the request is created by
begin_request
|
http_version |
The HTTP protocol version used to send the request.
It is set after the request is created by
begin_request.
|
resp represents an HTTP response.
CREATE TYPE resp AS ( status_code integer, reason_phrase varchar(256), http_version varchar(64) );
Table G.99. resp Parameters
| Parameter | Description |
|---|---|
status_code |
The status code returned by the web server. It is a 3-digit
integer that indicates the results of the HTTP request
as handled by the web server. It is set after the response
is processed by
get_response.
|
reason_phrase |
The short textual message returned by the web server that
describes the status code. It gives a brief description
of the results of the HTTP request as handled by the web server.
It is set after the response is processed by
get_response.
|
http_version |
The HTTP protocol version used in the HTTP response.
It is set after the response is processed by
get_response.
|
The cookie type represents an HTTP cookie.
The cookie_table type represents a collection of HTTP cookies.
It is essentially an array data type created on the basis of the
array created automatically.
CREATE TYPE cookie AS ( name varchar(256), value varchar(1024), domain varchar(256), expire timestamp with time zone, path varchar(1024), secure bool, version int, comment varchar(1024) ); CREATE DOMAIN cookie_table AS _cookie;
Table G.100. Fields of cookie and cookie_table
| Parameter | Description |
|---|---|
name | The name of the HTTP cookie. |
value | The value of the cookie. |
domain | The domain for which the cookie is valid. |
expire | The time by which the cookie will expire. |
path | The subset of URLs to which the cookie applies. |
secure | Should the cookie be returned to the web server using secured means only. |
version | The version of the HTTP cookie specification the cookie conforms. |
comment | The comment that describes the intended use of the cookie. |
The request_context_key type is used to define the key
to a request context. In Postgres Pro,
it is represented by integer and preserved for the
reasons of compatibility when migrating from
Oracle.
Note that the request_context in functions
and procedures below is preserved for the reasons of compatibility
when migrating from Oracle, and does not
affect the result.
request_function
and request_pieces_function
take a string URL, contact that site, and return the data
(typically HTML) obtained from that site.
request(url text, proxy text default null) returns text
#Fetches a web page. This function returns the first 2000 bytes of the page at most.
request_pieces(url text,
max_pieces int default 32767,
proxy text default null) returns text
#
This function returns a PL/pgSQL table of 2000-byte pieces
of the data retrieved from the given URL. The elements of
the table returned by request_pieces are
successive pieces of the data obtained from the HTTP request
to that URL.
utl_http provides functions and procedures to manipulate the configuration and default behavior when HTTP requests are executed within a database user session. When a request is created, it inherits the default settings of the HTTP cookie support, follow-redirect, body character set, and transfer timeout of the current session. When a response is created for a request, it inherits those settings from the request.
set_response_error_check(enable bool default false)
#
This procedure sets whether or not
get_response
raises an exception when the web server returns a status code
that indicates an error — a status code in the 4xx or 5xx range.
get_response_error_check(enable bool)
#This procedure checks if the response error check is set or not.
set_transfer_timeout(timeout int4 default 60)
#This procedure sets the default timeout value for all future HTTP requests that utl_http should attempt while reading the HTTP response from the web server or proxy server. This timeout value may be used to avoid the programs from being blocked by busy web servers or heavy network traffic while retrieving web pages from the web servers. The default value of the timeout is 60 seconds.
get_transfer_timeout(timeout int4)
#This procedure retrieves the default timeout value for all future HTTP requests.
set_detailed_excp_support(enable bool default false)
#
This procedure sets whether utl_http
raises a detailed exception. By default, it raises the
REQUEST_FAILED exception when an HTTP request
fails. Use
get_detailed_sqlcode
and get_detailed_sqlerrm
for more detailed information about the error.
The available exceptions are listed in Table G.101.
Table G.101. utl_http Exceptions
| Exception | Error Code | Reason | Where Raised |
|---|---|---|---|
BAD_ARGUMENT | 29265 | The argument passed to the interface is bad | Any HTTP request or response interface when detailed exception is enabled |
HEADER_NOT_FOUND | 29261 | The header is not found | get_header,
get_header_by_name
when detailed exception is enabled |
END_OF_BODY | 29266 | The end of HTTP response body is reached | read_raw,
read_text, and
read_line
when detailed exception is enabled |
HTTP_CLIENT_ERROR | 29268 | From get_response
the response status code indicates that a client error has occurred
(status code in 4xx range). From
begin_request
the HTTP proxy returns a status code in the 4xx range
when making an HTTPS request through the proxy. | get_response,
begin_request
when detailed exception is enabled |
HTTP_SERVER_ERROR | 29269 | From get_response
the response status code indicates that a server error has occurred
(status code in 5xx range). From begin_request
the HTTP proxy returns a status code in the 5xx range when making
an HTTPS request through the proxy. | get_response,
begin_request
when detailed exception is enabled |
REQUEST_FAILED | 29273 | The request fails to execute | Any HTTP request or response interface when detailed exception is disabled |
get_detailed_excp_support(enable bool)
#This procedure checks if utl_http will raise a detailed exception or not.
utl_http provides functions and procedures to begin an HTTP request, manipulate attributes, and send the request information to the web server. When a request is created, it inherits the default settings of the HTTP cookie support, follow-redirect, body character set, and transfer timeout of the current session. The settings can be changed by calling the request interface.
begin_request(url text,
method text default 'GET',
http_version text default null,
request_context request_context_key default null) returns req
#This function begins a new HTTP request.
set_header(r req,
name text,
value text)
#This procedure sets the HTTP request header for the future request.
set_authentication(r req,
username text,
password text,
scheme text default 'Basic',
for_proxy boolean default false)
#This procedure sets HTTP authentication information in the HTTP request header. The web server needs this information to authorize the request.
set_body_charset(r req,
charset name default null)
#
This procedure sets the character set when the media
type is text but the character set is not
specified in the Content-Type header
and may take one of the following forms:
Sets the default character set of the body of all future HTTP requests.
set_body_charset( charset IN name DEFAULT NULL)
Sets the character set of the request body.
set_body_charset( r INOUT req, charset IN name DEFAULT NULL)
set_cookie_support(r req,
enable bool)
#This procedure determines cookie support and may take one of the following forms:
Enables or disables support for the HTTP cookies in the request.
set_cookie_support( r INOUT req, enable IN bool DEFAULT true)
Sets whether future HTTP requests will support HTTP cookies, and the maximum number of cookies maintained in the current database user session.
set_cookie_support( enable IN bool, max_cookies IN int4 DEFAULT 300, max_cookies_per_site IN int4 DEFAULT 20)
set_follow_redirect(r req,
max_redirects int4 default 3)
#
This procedure sets the maximum number of times
utl_http should follow HTTP redirect
instruction in the HTTP responses to requests in
get_response.
Default is 3.
set_proxy(proxy text,
no_proxy_domains text)
#This procedure sets the proxy to be used for requests of HTTP or other protocols. Note that proxy with no valid certificate will not work properly.
write_raw(r req,
data bytea)
#This procedure writes binary data in the HTTP request body for the future request.
write_text(r req,
data bytea)
#This procedure writes text data in the HTTP request body for the future request.
end_request(r req)
#This procedure ends the HTTP request by resetting request parameters.
set_option(text text)
#Set options for all future requests in this session.
PROCEDURE set_option(
option IN text,
value IN text
);
set_option(r req text text)
#Set option for the specified request.
PROCEDURE set_option(
r IN req,
option IN text,
value IN text
);
get_option(text)
#Show the default value set for all future requests in this session.
FUNCTION get_option(
option IN text
) RETURNS text;
get_option(r req text)
#Show the default option value set for an existing request.
FUNCTION get_option(
r IN req,
option IN text
)
These functions have the following options:
OPT_SSL_VERIFYPEER — verify the peer's SSL certificate.
It can be specified for a request or as a defult value for future requests.
Possible values are 0 or 1 (default).
OPT_SSL_VERIFYHOST — verify the certificate's name against host.
It can be specified for a request or as a defult value for future requests.
This option is available only for libcurl
version 7.8.1 or later.
Possible values are 0, 1, or
2 (default). When the option is set to
0, the connection succeeds regardless of the names
in the certificate. Use this value with caution.
It is also not recommended to use the 1 value, as it
may lead to unexpected results depending on the libcurl
version. For more information, see the
libcurl official documentation.
utl_http provides functions and procedures
to manipulate an HTTP response obtained from
get_response
and receive response information from the web server. When a response
is created for a request, it inherits settings of the HTTP cookie support,
follow-redirect, body character set, and transfer timeout from the
request. Only the body character set can be changed by calling the
response interface.
end_response(r resp)
#This procedure ends the HTTP response by resetting request parameters.
get_authentication(r resp,
scheme text,
realm text,
for_proxy bool default false)
#This procedure retrieves the HTTP authentication information needed for the request to be accepted by the web server as indicated in the HTTP response header.
get_header(r resp,
n int4,
name text,
value text)
#This procedure returns the n-th HTTP response header name and value returned in the response.
get_header_by_name(r resp,
name text,
value text,
n int4 default 1)
#This procedure returns the HTTP response header value returned in the response given the name of the header.
get_header_count(r resp) returns int4
#This function returns the number of HTTP response headers returned in the response.
get_response(r req,
return_info_response bool default false) returns resp
#This function completes the HTTP request and response: reads the HTTP response and processes the status line and response headers. The status code, reason phrase and the HTTP protocol version are stored in the response record.
read_raw(r resp,
data bytea,
len int4 default null)
#This procedure reads the HTTP response body in binary form and returns the output in the caller-supplied buffer.
read_line(r resp,
data text,
remove_crlf bool default false)
#This procedure reads the HTTP response body in text form until the end of line is reached and returns the output in the caller-supplied buffer.
read_text(r resp,
data bytea,
len int4 default null)
#This procedure reads the HTTP response body in text form and returns the output in the caller-supplied buffer.
utl_http provides functions and procedures to manipulate HTTP cookies.
add_cookies(cookies cookie_table,
request_context request_context_key default null)
#This procedure adds the cookies maintained by utl_http.
clear_cookies(request_context request_context_key default null)
#This procedure clears all the cookies currently maintained by utl_http.
get_cookie_count(request_context request_context_key default null) returns int4
#This function returns the number of cookies currently maintained by utl_http set by all web servers.
get_cookies(cookies cookie_table,
request_context request_context_key default null) returns cookie_table
#This function returns all the number of cookies currently maintained by utl_http set by all web servers.
utl_http provides functions to retrieve error information.
get_detailed_sqlcode() returns int4
#
Retrieves the detailed SQLCODE of the last
exception raised (see Table G.101).
get_detailed_sqlerrm() returns text
#
Retrieves the detailed SQLERRM of the last
exception raised (see Table G.101).
DO $$
DECLARE
request utl_http.req;
response utl_http.resp;
text_body text;
BEGIN
CALL utl_http.set_body_charset('WIN1251');
request := utl_http.begin_request('https://postgrespro.ru/', 'GET');
CALL utl_http.set_authentication(request, 'admin', 'qwerty', 'Basic', FALSE);
response := utl_http.get_response(request);
CALL utl_http.read_text(response, text_body);
text_body = substring(text_body FROM 720 FOR 245);
RAISE NOTICE '%', text_body;
END$$;
You can specify the utl_http schema in the
search_path parameter explicitly to omit it in the body
of a request:
SET search_path =utl_http, public;
The example above will then look as follows:
DO $$
DECLARE
request req;
response resp;
text_body text;
BEGIN
CALL set_body_charset('WIN1251');
request := begin_request('https://postgrespro.ru/docs/enterprise/17/utl-http', 'GET');
CALL set_authentication(request, 'admin', 'qwerty', 'Basic', FALSE);
response := get_response(request);
CALL read_text(response, text_body);
text_body = substring(text_body FROM 720 FOR 245);
RAISE NOTICE '%', text_body;
END$$;
Example for a self-signed certificate:
test=# SELECT * FROM utl_http.request('https://localhost:5001');
ERROR: utl_http failed while handling the request to "https://localhost:5001".
Details: "SSL peer certificate or SSH remote key was not OK"
test=# call utl_http.set_option('OPT_SSL_VERIFYPEER', '0');
test=# call utl_http.set_option('OPT_SSL_VERIFYHOST', '0');
test=# SELECT * FROM substr(utl_http.request('https://localhost:5001'), 0, 50);
substr
------------------
<!DOCTYPE html> +
<html lang="en">+
+
<head> +
<met
(1 row)
Example of client authentication with key:
SELECT * FROM utl_http.begin_request('https://some_server');
CALL utl_http.set_option((NULL, NULL, NULL), 'OPT_CAINFO_BLOB', '-----BEGIN CERTIFICATE-----
...
Y7707nS0spc1qVPMSQ==
-----END CERTIFICATE-----
');
CALL utl_http.set_option((NULL, NULL, NULL), 'OPT_SSLCERT_BLOB', '-----BEGIN CERTIFICATE-----
...
GMNTQVzSHmuu8tw5W4GjNUQL2Wx5h/yuMD5dS+vCeQ==
-----END CERTIFICATE-----
');
CALL utl_http.set_option((NULL, NULL, NULL), 'OPT_SSLKEY_BLOB', '-----BEGIN RSA PRIVATE KEY-----
Proc-Type: 4,ENCRYPTED
DEK-Info: AES-256-CBC,2557386B35596227304F2F017F07B467
...
-----END RSA PRIVATE KEY-----
');
CALL utl_http.set_option((NULL, NULL, NULL), 'OPT_KEYPASSWD', 'superpassword');
SELECT * FROM utl_http.get_response((NULL, NULL, NULL));