http — HTTP Client

Make HTTP requests directly from SQL. Functions are available in two calling conventions: scalar (returns JSONB) and table (returns rows).

Installation

The http extension is pre-enabled — no CREATE EXTENSION required.

Scalar Functions (JSONB)

The scalar form returns a single JSONB value containing the full response. This is the recommended calling convention for new code.

SQL

-- GET request — returns JSONB
SELECT http_get('https://api.example.com/data');

-- Extract fields from the JSONB response
SELECT (http_get('https://httpbin.org/get'))->>'status' AS status;

-- POST with JSON body
SELECT http_post(
  'https://api.example.com/endpoint',
  '{"key": "value"}',
  'application/json'
);

-- Parse the response content as JSON
SELECT (http_get('https://api.ipify.org?format=json')->>'content')::json->>'ip' AS my_ip;

Scalar Response Shape

The JSONB object contains:

Key	Type	Description
status	number	HTTP status code (200, 404, etc.).
content	string	Response body text.
content_type	string	Response Content-Type header.
headers	array	Response headers as [{"field": "...", "value": "..."}, ...].

Available Scalar Functions

Function	Description
http_get(url, headers?)	HTTP GET, returns JSONB. Optional headers JSONB.
http_post(url, body, content_type, headers?)	HTTP POST, returns JSONB. Optional headers JSONB.
http_put(url, body, content_type, headers?)	HTTP PUT, returns JSONB. Optional headers JSONB.
http_delete(url, headers?)	HTTP DELETE, returns JSONB. Optional headers JSONB.
http_head(url, headers?)	HTTP HEAD, returns JSONB. Optional headers JSONB.
http_patch(url, body, content_type, headers?)	HTTP PATCH, returns JSONB. Optional headers JSONB.
http(method, url, headers?, content_type?, body?)	Generic HTTP request with full control, returns JSONB. Only method and url are required. method must be one of GET, POST, PUT, DELETE, HEAD, PATCH, matched case-insensitively (get and Get both work). Any other verb — and a value with surrounding whitespace — raises HTTP method is unsupported (22023).

Table Functions (Row)

The table-valued form requires the schema qualifier

Which calling convention you get depends on how you qualify the name:

Call	Result
SELECT http_get(url)	Scalar — returns JSONB (used by every example on this page)
SELECT * FROM extensions.http_get(url)	Table-valued — returns status, content_type, headers, content columns
SELECT * FROM http_get(url) (unqualified)	ERROR: function http_get(unknown) does not exist (42883)
SELECT extensions.http_get(url) (qualified scalar)	ERROR: function extensions.http_get(unknown) does not exist (42883)

The argument type printed in those two messages follows how you spell the URL. A bare string literal is untyped, so the message reads (unknown); an explicit 'https://example.com'::text cast or a column reference makes it (text). The missing function is the same either way.

So use the bare name for the scalar JSONB form, and the extensions. prefix for the row form:

SQL

SELECT status, content_type FROM extensions.http_get('https://example.com');

The same schema-qualifier rule applies to extensions.http_post(...) and the other verbs.

Real-World Examples

POST to Webhook

SQL

-- Send alert to webhook
SELECT http_post(
  'https://hooks.slack.com/services/xxx/yyy/zzz',
  '{"text": "Database backup completed!"}',
  'application/json'
);

Check URL Status

One statement is enough when every endpoint answers. status carries the HTTP status code, so 4xx and 5xx replies come back as ordinary values:

SQL

-- Status codes for endpoints that all respond
SELECT
  url,
  (http_head(url)->>'status')::int AS status
FROM (VALUES
  ('https://db9.ai'),
  ('https://example.com'),
  ('https://httpbin.org/status/503')
) t(url);

status reports replies, not failures

There is no status when the server never replied. Every transport-level failure — DNS, connect, timeout, redirect limit, response size, non-UTF-8 body — raises instead, and that error aborts the whole statement, discarding the rows that had already succeeded:

SQL

SELECT url, (http_head(url)->>'status')::int AS status
FROM (VALUES
  ('https://db9.ai'),
  ('https://nxdomain.invalid')
) t(url);
-- ERROR:  could not resolve HTTP resource host  (58030)
-- Zero rows. The result for https://db9.ai is lost as well.

Fail-fast is the right default for a query whose result depends on every call, but it is the wrong shape for a health check, where an endpoint being down is the expected outcome. Use the per-URL pattern below for that.

Health Check That Survives Unreachable Endpoints

Move the request into a PL/pgSQL function with its own EXCEPTION handler. Each URL is then isolated: a raise is turned into a row instead of ending the statement.

SQL

CREATE OR REPLACE FUNCTION http_probe(u TEXT)
RETURNS TABLE (status INT, error TEXT)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT (http_head(u)->>'status')::int, NULL::text;
EXCEPTION WHEN OTHERS THEN
  RETURN QUERY SELECT NULL::int, SQLERRM;
END $$;

SELECT t.url, p.status, p.error
FROM (VALUES
  ('https://db9.ai'),
  ('https://nxdomain.invalid'),
  ('https://httpbin.org/status/503')
) t(url),
LATERAL http_probe(t.url) p;

              url               | status |                error
--------------------------------+--------+--------------------------------------
 https://db9.ai                 |    200 |
 https://nxdomain.invalid       |        | could not resolve HTTP resource host
 https://httpbin.org/status/503 |    503 |
(3 rows)

An endpoint that replied puts its code in status — including 503 and every other 4xx/5xx. An endpoint that could not be reached at all puts the reason in error. Both kinds of row survive, so the check reports on all three endpoints rather than stopping at the first one that is down.

SSRF protection is built-in

DB9 blocks outbound HTTP requests to private IP ranges (RFC 1918: 10.0.0.0/8, the whole 172.16.0.0/12 span from 172.16.x through 172.31.x, and 192.168.0.0/16), loopback (127.0.0.0/8), and link-local addresses. Only public HTTPS endpoints are reachable. HTTP (non-TLS) is also blocked.

Custom Headers

Pass request headers as a JSONB value. Two formats are supported:

SQL

-- Object format (recommended)
'{"Authorization": "Bearer sk-..."}'::jsonb

-- Array format (pgsql-http compatible)
'[{"field": "Authorization", "value": "Bearer sk-..."}]'::jsonb

Bearer Token Authentication

SQL

-- GET with Bearer token
SELECT
  (http_get(
    'https://api.example.com/protected',
    '{"Authorization": "Bearer sk-your-token-here"}'::jsonb
  ))->>'content' AS body;

-- POST with Bearer token
SELECT
  (http_post(
    'https://api.example.com/data',
    '{"event": "user_signup"}',
    'application/json',
    '{"Authorization": "Bearer sk-your-token-here"}'::jsonb
  ))->>'status' AS status;

API Key Header

SQL

-- X-API-Key header
SELECT
  (http_post(
    'https://api.example.com/webhook',
    '{"event": "test"}',
    'application/json',
    '{"X-API-Key": "my-api-key"}'::jsonb
  ))->>'status' AS status;

-- Vendor-specific key header (e.g. OpenAI, Anthropic)
SELECT
  (http_post(
    'https://api.openai.com/v1/chat/completions',
    '{"model": "gpt-4o-mini", "messages": [{"role": "user", "content": "Hello"}]}',
    'application/json',
    '{"Authorization": "Bearer sk-proj-...", "OpenAI-Organization": "org-..."}'::jsonb
  ))->>'content' AS response;

Multiple Headers

SQL

-- Pass several headers at once (object format)
SELECT http_get(
  'https://api.example.com/data',
  '{"Authorization": "Bearer token", "X-Request-ID": "req-001", "Accept": "application/json"}'::jsonb
);

-- Using the generic http() function for full control
SELECT http(
  'POST',
  'https://api.example.com/endpoint',
  '{"Authorization": "Bearer token", "X-Idempotency-Key": "key-123"}'::jsonb,
  'application/json',
  '{"payload": "data"}'
);

Store a token in a table for reuse

To avoid repeating credentials inline, store them in a table and join at query time:

SQL

CREATE TABLE api_credentials (
  name TEXT PRIMARY KEY,
  headers JSONB NOT NULL
);

INSERT INTO api_credentials VALUES (
  'my-api',
  '{"Authorization": "Bearer sk-your-token"}'::jsonb
);

-- Use stored headers in a request
SELECT http_get('https://api.example.com/data', headers)
FROM api_credentials
WHERE name = 'my-api';

Security note: Credentials stored in tables are visible to any role with SELECT access. Use row-level security or store secrets outside the database for production workloads.

Error Handling

DB9 splits HTTP outcomes into two channels, and they are handled differently:

• The server replied — any code, including 4xx and 5xx, arrives in the status field as an ordinary value. Branch on it in SQL.
• The server never replied — DNS, connect, timeout, redirect limit, response size and non-UTF-8 failures raise a PostgreSQL error and abort the statement. No status is produced, so no amount of status checking catches them. Use EXCEPTION handling, as in Health Check That Survives Unreachable Endpoints.

Check HTTP status codes

The status field in the JSONB response contains the HTTP status code. Check it before using the response body:

SQL

-- Check status before using the response
SELECT
  CASE
    WHEN (response->>'status')::int = 200 THEN response->>'content'
    WHEN (response->>'status')::int = 401 THEN 'Unauthorized — check your API key'
    WHEN (response->>'status')::int = 429 THEN 'Rate limited — retry later'
    ELSE 'Error: HTTP ' || (response->>'status')::int
  END AS result
FROM (SELECT http_get('https://api.example.com/data') AS response) r;

Filter to successful responses only

SQL

-- Only process rows where the API returned 200
SELECT url, (response->>'content')::jsonb AS data
FROM my_urls
CROSS JOIN LATERAL (SELECT http_get(my_urls.url) AS response) r
WHERE (response->>'status')::int = 200;

The WHERE clause filters replies, so a row that returned 404 or 500 is dropped as intended. It cannot filter out a row whose host is unreachable: that row raises, and the raise takes the whole query with it. When my_urls may contain an unreachable host, narrow the list with http_probe() first and fetch only the URLs that answered:

SQL

SELECT m.url, p.status
FROM my_urls m,
LATERAL http_probe(m.url) p
WHERE p.status = 200;

Raise an exception on failure

Use RAISE EXCEPTION inside a function or DO block to abort on non-200:

SQL

DO $$
DECLARE
  response JSONB;
  status_code INT;
BEGIN
  response := http_post(
    'https://api.example.com/notify',
    '{"event": "test"}',
    'application/json',
    '{"Authorization": "Bearer sk-..."}'::jsonb
  );
  status_code := (response->>'status')::int;
  IF status_code < 200 OR status_code >= 300 THEN
    RAISE EXCEPTION 'HTTP request failed with status %: %',
      status_code, response->>'content';
  END IF;
END $$;

Handle timeouts and other transport failures

A request that exceeds 5 seconds raises a PostgreSQL error, and so does every other failure that produces no reply. EXCEPTION WHEN OTHERS catches the whole class, which is why the same handler works for a timeout and for a host that does not resolve:

SQL

DO $$
BEGIN
  PERFORM http_get('https://slow-api.example.com/data');
EXCEPTION WHEN OTHERS THEN
  RAISE WARNING 'HTTP request failed: %', SQLERRM;
END $$;

SQLERRM reports whichever failure occurred, so read it rather than assuming a timeout — the placeholder host above does not resolve, so it warns HTTP request failed: could not resolve HTTP resource host.

Common error messages

Error messages never include the request URL, host or port, so identify the failing call from the statement itself.

Error	SQLSTATE	Cause
insecure HTTP requests are disabled	42501	Plain http:// URL — use https://
only HTTP and HTTPS URL schemes are supported	22023	The URL names some other scheme, such as ftp://
URL host or address is not allowed by network policy	42501	SSRF protection blocked the hostname, or the IP address it names or resolves to (loopback, private, link-local)
URL port is not allowed by network policy	42501	The URL names a port other than 443. The port is checked before the address, so a blocked IP on a blocked port reports this message rather than the network-policy one
URL user information is not allowed	42501	The URL contains user:pass@ credentials
could not resolve HTTP resource host	58030	DNS lookup for the host failed
could not connect to HTTP resource	58030	The connection could not be made within the 1-second connect timeout, or no response arrived within the 5-second request timeout. A slow endpoint and an unreachable one produce the same message
HTTP redirect limit exceeded (max 3)	58030	The redirect chain was longer than 3 hops
HTTP response stream failed	58030	The response started but its body was still arriving when the 5-second request timeout expired
HTTP response exceeds the 1048576-byte limit	54000	Response body exceeded the 1 MB limit
HTTP response is not valid UTF-8	58030	The response body is binary or in a non-UTF-8 encoding. Encoding is validated as the body streams in, so an oversized binary body reports this rather than the size limit
HTTP request body exceeds the 262144-byte limit	54000	Request body exceeded the 256 KB limit
http request limit exceeded (max 100 per statement)	54000	The statement tried to issue more than 100 http calls

Safety Boundaries

Limit	Value
Protocol	HTTPS only (HTTP blocked)
Max requests per statement	100
Max concurrent requests	20 in flight per tenant per node (not per statement)
Max response size	1 MB (1048576 bytes)
Max request body	256 KB (262144 bytes)
Max redirects	3
Request timeout	5 seconds (1-second connect timeout)
SSRF protection	Private/internal IP ranges blocked

These limits are fixed and cannot be raised with session parameters.

Requests inside one statement run serially

The two request caps measure different things. The 20-request concurrency limit governs how many requests a tenant may have in flight across its sessions. It is not intra-statement parallelism: inside a single statement, http calls are issued one after another, so wall-clock time is the sum of the individual requests. Measured against https://httpbin.org/delay/2 over the PostgreSQL wire protocol:

http calls in one statement	Wall clock
1	2.55 s
5	11.98 s
10	24.20 s
20	46.89 s

Serial execution is what keeps the concurrency cap meaningful: one statement holds one request open at a time however many rows it fans out over, so a single query can never consume the tenant’s whole budget and starve other sessions of network capacity. What you give up is latency hiding, which has two practical consequences:

• Budget a statement as rows × per-request latency, not as one round trip. The per-statement cap of 100 bounds the request count, not the duration.
• db9 db sql and the HTTP SQL API stop waiting after roughly 15 seconds and return Error 504: upstream request timeout. The statement is not stopped: it keeps making its calls and can still commit, but its result never reaches you, and later statements in the same request are not run. Fast endpoints still fit — 100 http_head() calls against https://example.com finished in 10.43 s — but 10 calls against a 2-second endpoint do not, while 6 (14.16 s) do. For fan-outs over slow endpoints, connect over the PostgreSQL wire protocol with db9 db connect, which does not apply that 15-second ceiling, or split the work into statements sent as separate requests — the ceiling applies to the whole request, not to each statement in it.

Next Steps

• HTTP from SQL Guide — Tutorial: call APIs, send webhooks, and enrich data from SQL
• Extensions Overview — Every built-in extension
• Scheduled Jobs with pg_cron — Combine HTTP calls with scheduled jobs