pg_net：异步网络

1
net.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
    -- the maximum number of milliseconds the request may take before being canceled
9
    timeout_milliseconds int default 2000
10
)
11
    -- request_id reference
12
    returns bigint
13
14
    strict
15
    volatile
16
    parallel safe
17
    language plpgsql

1
select
2
    net.http_get('https://news.ycombinator.com')
3
    as request_id;
4
request_id
5
----------
6
         1
7
(1 row)

1
net.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
    -- the maximum number of milliseconds the request may take before being canceled
11
    timeout_milliseconds int default 2000
12
)
13
    -- request_id reference
14
    returns bigint
15
16
    volatile
17
    parallel safe
18
    language plpgsql

1
select
2
    net.http_post(
3
        url:='https://httpbin.org/post',
4
        body:='{"hello": "world"}'::jsonb
5
    ) as request_id;
6
request_id
7
----------
8
         1
9
(1 row)

1
net.http_delete(
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
    -- the maximum number of milliseconds the request may take before being canceled
9
    timeout_milliseconds int default 2000
10
)
11
    -- request_id reference
12
    returns bigint
13
14
    strict
15
    volatile
16
    parallel safe
17
    language plpgsql
18
    security definer

1
select
2
    net.http_delete(
3
        'https://dummy.restapiexample.com/api/v1/delete/2'
4
    ) as request_id;
5
----------
6
         1
7
(1 row)

1
CREATE UNLOGGED TABLE
2
    net.http_request_queue (
3
        id bigint NOT NULL DEFAULT nextval('net.http_request_queue_id_seq'::regclass),
4
        method text NOT NULL,
5
        url text NOT NULL,
6
        headers jsonb NOT NULL,
7
        body bytea NULL,
8
        timeout_milliseconds integer NOT NULL
9
    )

1
CREATE UNLOGGED TABLE
2
    net._http_response (
3
        id bigint NULL,
4
        status_code integer NULL,
5
        content_type text NULL,
6
        headers jsonb NULL,
7
        content text NULL,
8
        timed_out boolean NULL,
9
        error_msg text NULL,
10
        created timestamp with time zone NOT NULL DEFAULT now()
11
    )

1
select * from net._http_response;

1
select
2
    net.http_post(
3
        url := 'https://postman-echo.com/post',
4
        body := '{"key1": "value", "key2": 5}'::jsonb
5
    ) as request_id;

1
select
2
    "content"
3
from net._http_response
4
where id = <request_id>
5
-- returns information about the request
6
-- including the body sent: {"key": "value", "key": 5}

1
create or replace function debugging_example (row_id int)
2
returns jsonb as $$
3
declare
4
    -- Store payload data
5
    row_data_var jsonb;
6
begin
7
    -- Retrieve row data and convert to JSON
8
    select to_jsonb("<example_table>".*) into row_data_var
9
    from "<example_table>"
10
    where "<example_table>".id = row_id;
11
12
    -- Initiate HTTP POST request to URL
13
    perform
14
        net.http_post(
15
            url := 'https://postman-echo.com/post',
16
            -- Use row data as payload
17
            body := row_data_var
18
        ) as request_id;
19
20
    -- Optionally Log row data or other data for inspection in Supabase Dashboard's Postgres Logs
21
    raise log 'Logging an entire row as JSON (%)', row_data_var;
22
23
    -- return row data to inspect
24
    return row_data_var;
25
26
-- Handle exceptions here if needed
27
exception
28
    when others then
29
        raise exception 'An error occurred: %', SQLERRM;
30
end;
31
$$ language plpgsql;
32
33
-- calling function
34
select debugging_example(<row_id>);

1
select
2
  *
3
from net._http_response
4
where "status_code" >= 400 or "error_msg" is not null
5
order by "created" desc;

1
select
2
  "name",
3
  "setting"
4
from pg_settings
5
where "name" like 'pg_net%';

1
alter system set pg_net.ttl to '24 hours';
2
select net.worker_restart();

1
select
2
    net.http_post(
3
        url:='https://project-ref.supabase.co/functions/v1/function-name',
4
        headers:='{"Content-Type": "application/json", "apikey": "<SUPABASE_PUBLISHABLE_KEY>"}'::jsonb,
5
        body:='{"name": "pg_net"}'::jsonb
6
    ) as request_id;

1
select cron.schedule(
2
	'cron-job-name',
3
	'* * * * *', -- Executes every minute (cron syntax)
4
	$$
5
	    -- SQL query
6
	    select "net"."http_post"(
7
            -- URL of Edge function
8
            url:='https://project-ref.supabase.co/functions/v1/function-name',
9
            headers:='{"apikey": "<SUPABASE_PUBLISHABLE_KEY>"}'::jsonb,
10
            body:='{"name": "pg_net"}'::jsonb
11
	    ) as "request_id";
12
	$$
13
);

1
-- function called by trigger
2
create or replace function <function_name>()
3
    returns trigger
4
    language plpgSQL
5
as $$
6
begin
7
    -- calls pg_net function net.http_post
8
    -- sends request to postman API
9
    perform "net"."http_post"(
10
      'https://postman-echo.com/post'::text,
11
      jsonb_build_object(
12
        'old_row', to_jsonb(old.*),
13
        'new_row', to_jsonb(new.*)
14
      ),
15
      headers:='{"Content-Type": "application/json"}'::jsonb
16
    ) as request_id;
17
    return new;
18
END $$;
19
20
-- trigger for table update
21
create trigger <trigger_name>
22
    after update on <table_name>
23
    for each row
24
    execute function <function_name>();

1
with "selected_table_rows" as (
2
    select
3
        -- Converts all the rows into a JSONB array
4
        jsonb_agg(to_jsonb(<table_name>.*)) as JSON_payload
5
    from <table_name>
6
    -- good practice to LIMIT the max amount of rows
7
)
8
select
9
    net.http_post(
10
        url := 'https://postman-echo.com/post'::text,
11
        body := JSON_payload
12
    ) AS request_id
13
FROM "selected_table_rows";

1
-- Ensure the target schema exists
2
create schema if not exists extensions;
3
4
-- Drop the existing extension
5
drop extension pg_net;
6
7
-- Re-create the extension in the extensions schema
8
create extension pg_net with schema extensions;