Postgres Pro Enterprise : Documentation: 16: G.8. utl_http — access data on the Internet over the HTTP protocol : Postgres Professional

G.8. utl_http — access data on the Internet over the HTTP protocol
Prev	Up	Appendix G. Postgres Pro Modules and Extensions Shipped as Individual Packages	Home	Next

G.8. utl_http — access data on the Internet over the HTTP protocol #

G.8.1. Installation
G.8.2. Data Types
G.8.3. utl_http Functions and Procedures
G.8.4. Example

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.

G.8.1. Installation #

The utl_http 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_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.

G.8.2. Data Types #

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.96. 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.97. 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.98. 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.

G.8.3. utl_http Functions and Procedures #

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.

G.8.3.1. Simple HTTP Fetches #

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.

G.8.3.2. Session Settings #

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.99.

Table G.99. 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.

G.8.3.3. HTTP Requests #

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 text) #

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.

G.8.3.4. Options and Requests #

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.

G.8.3.5. HTTP Responses #

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 text, len int4 default null) #: This procedure reads the HTTP response body in text form and returns the output in the caller-supplied buffer.

G.8.3.6. HTTP Cookies #

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.

G.8.3.7. Error Conditions #

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.99).
get_detailed_sqlerrm() returns text #: Retrieves the detailed SQLERRM of the last exception raised (see Table G.99).

G.8.4. Example #

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));

Prev	Up	Next
G.7. sr_plan — save a specific plan of a parameterized query for future usage	Home	G.9. utl_mail — manage emails

epub pdf