pg_net: Async Networking
caution
The pg_net API is in beta. Functions signatures may change.
pg_net is a PostgreSQL extension exposing a SQL interface for async networking with a focus on scalability and UX.
It differs from the http
extension in that it is asynchronous by default. This makes it useful in blocking functions (like triggers).
Usage#
Enable the extension#
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "pg_net" and enable the extension.
http_get
#
Creates an HTTP GET request returning the request's ID. HTTP requests are not started until the transaction is committed.
Signature#
caution
This is a Postgres SECURITY DEFINER function.
1net.http_get(
2 -- url for the request
3 url text,
4 -- key/value pairs to be url encoded and appended to the `url`
5 params jsonb default '{}'::jsonb,
6 -- key/values to be included in request headers
7 headers jsonb default '{}'::jsonb,
8 -- WARNING: this is currently ignored, so there is no timeout
9 -- the maximum number of milliseconds the request may take before being cancelled
10 timeout_milliseconds int default 1000
11)
12 -- request_id reference
13 returns bigint
14
15 strict
16 volatile
17 parallel safe
18 language plpgsql
Usage#
1select net.http_get('https://news.ycombinator.com') as request_id;
2request_id
3----------
4 1
5(1 row)
After triggering http_get
, use http_get_result
to get the result of the request.
http_post
#
Creates an HTTP POST request with a JSON body, returning the request's ID. HTTP requests are not started until the transaction is committed.
The body's character set encoding matches the database's server_encoding
setting.
Signature#
caution
This is a Postgres SECURITY DEFINER function
1net.http_post(
2 -- url for the request
3 url text,
4 -- body of the POST request
5 body jsonb default '{}'::jsonb,
6 -- key/value pairs to be url encoded and appended to the `url`
7 params jsonb default '{}'::jsonb,
8 -- key/values to be included in request headers
9 headers jsonb default '{"Content-Type": "application/json"}'::jsonb,
10 -- WARNING: this is currently ignored, so there is no timeout
11 -- the maximum number of milliseconds the request may take before being cancelled
12 timeout_milliseconds int default 1000
13)
14 -- request_id reference
15 returns bigint
16
17 volatile
18 parallel safe
19 language plpgsql
Usage#
1select
2 net.http_post(
3 url:='https://httpbin.org/post',
4 body:='{"hello": "world"}'::jsonb
5 ) as request_id;
6request_id
7----------
8 1
9(1 row)
After triggering http_post
, use http_get_result
to get the result of the request.
http_collect_response
#
Given a request_id
reference, retrieves the response.
When async:=false
is set it is recommended that statement_timeout is set for the maximum amount of time the caller is willing to wait in case the response is slow to populate.
Signature#
caution
This is a Postgres SECURITY DEFINER function
1net.http_collect_response( 2 -- request_id reference 3 request_id bigint, 4 -- when `true`, return immediately. when `false` wait for the request to complete before returning 5 async bool default true 6) 7 -- http response composite wrapped in a result type 8 returns net.http_response_result 9 10 strict 11 volatile 12 parallel safe
Usage#
caution
net.http_collect_response
must be in a separate transaction from the calls to net.http_<method>
1select
2 net.http_post(
3 url:='https://httpbin.org/post',
4 body:='{"hello": "world"}'::jsonb
5 ) as request_id;
6request_id
7----------
8 1
9(1 row)
10
11select * from net.http_collect_response(1, async:=false);
12status | message | response
13--------+---------+----------
14SUCCESS ok (
15 status_code := 200,
16 headers := '{"date": ...}',
17 body := '{"args": ...}'
18 )::net.http_response_result
19
20
21select
22 (response).body::json
23from
24 net.http_collect_response(request_id:=1);
25 body
26-------------------------------------------------------------------
27 {
28 "args": {},
29 "data": "{\"hello\": \"world\"}",
30 "files": {},
31 "form": {},
32 "headers": {
33 "Accept": "*/*",
34 "Content-Length": "18",
35 "Content-Type": "application/json",
36 "Host": "httpbin.org",
37 "User-Agent": "pg_net/0.2",
38 "X-Amzn-Trace-Id": "Root=1-61031a5c-7e1afeae69bffa8614d8e48e"
39 },
40 "json": {
41 "hello": "world"
42 },
43 "origin": "135.63.38.488",
44 "url": "https://httpbin.org/post"
45 }
46(1 row)
Where response
is a composite:
1status_code integer 2headers jsonb 3body text
Possible values for net.http_response_result.status
are ('PENDING', 'SUCCESS', 'ERROR')
Resources#
- Source code: github.com/supabase/pg_net
- Official Docs: supabase.github.io/pg_net