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.

Table Functions (Row)

Table-valued form not supported

The table-valued calling convention (SELECT status FROM http_get(url)) is not available in DB9. Calling http_get as a set-returning function returns:

ERROR: unsupported table-valued function: http_get (0A000)

Use the scalar JSONB form instead — all examples in this page use the supported scalar form.

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

SQL

-- Health check multiple endpoints
SELECT
  url,
  (http_head(url)->>'status')::int AS status
FROM (VALUES
  ('https://api.example.com/health'),
  ('https://db9.ai'),
  ('https://google.com')
) t(url);

SSRF protection is built-in

DB9 blocks outbound HTTP requests to private IP ranges (RFC 1918: 10.x, 172.16.x, 192.168.x), loopback (127.x), 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

Check HTTP status codes

The status field in the JSONB response contains the HTTP status code. Always 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;

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

Requests that exceed 5 seconds raise a PostgreSQL error. Catch it with EXCEPTION:

SQL

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

Common error messages

Error	Cause
http: insecure http requests are disabled	Plain HTTP URL — use https://
http: host is not allowed	SSRF protection blocked the hostname
http: ip is not allowed	SSRF protection blocked the resolved IP
http: timeout	Request exceeded the 5-second timeout
http: response too large	Response body exceeded 1 MB limit

Safety Boundaries

Limit	Value
Protocol	HTTPS only (HTTP blocked)
Max requests per statement	100
Max concurrent requests	20
Max response size	1 MB
Request timeout	5 seconds
SSRF protection	Private/internal IP ranges blocked

Next Steps

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