Using pg_curl with pgEdge

How to make pgEdge make https request back to your application

If you need pgEdge to make https requests back to your application based on changes to data, you can easily install pg_curl, set up a trigger on the desired table, and configure that trigger function to make the curl request from the database that you need.

To test the POST command I made a very simple python3 http.server that has a do_POST function that is running on the same server and can be accessed by the url localhost:8080.

Install pg_curl

./nc um install curl-pg15

Create the following post function in your pgEdge database. For another trigger function you would want to modify what inputs are passed to curl_easy_setopt_postfields (your JSON dataload) and curl_easy_setopt_url (your url).

CREATE OR REPLACE FUNCTION post() RETURNS TRIGGER AS $pgbench_branches$

DECLARE

curl_post VARCHAR;

BEGIN

WITH s AS (SELECT

curl_easy_reset(),

curl_easy_setopt_postfields(convert_to('{"bbalance":' || NEW.bbalance || '}', 'utf-8')),

curl_easy_setopt_url('http://localhost:8000'),

curl_header_append('Content-Type', 'application/json; charset=utf-8'),

curl_easy_perform(),

curl_easy_getinfo_data_in()

) SELECT convert_from(curl_easy_getinfo_data_in, 'utf-8') FROM s INTO curl_post;

raise notice '%', curl_post;

RETURN NULL;
END;

$pgbench_branches$ LANGUAGE plpgsql;

Create the following trigger on your pgbench_branches table

CREATE TRIGGER post_update

AFTER UPDATE ON pgbench_branches

FOR EACH ROW

WHEN (OLD.* IS DISTINCT FROM NEW.*)

EXECUTE FUNCTION post();

Update the pgbranches_table

UPDATE pgbench_branches SET bbalance=15 WHERE bid=1;

NOTICE: This is POST request. Received: {"bbalance":15}

UPDATE 1

In addition to this raise notice, the server shows that the post was received:

127.0.0.1 - - [05/Mar/2023 16:09:40] "POST / HTTP/1.1" 200 -