Resep PostgreSQL: cURL: dapatkan, kirim dan ... email

Untuk menyiapkan cURL di PostgreSQL, kita perlu postgres itu sendiri dan ekstensi pg_curl . (Saya memberi tautan ke garpu postgres saya, karena saya membuat beberapa perubahan yang belum dijejalkan ke repositori asli. Anda juga dapat menggunakan gambar yang sudah jadi .)

Untuk memulai, instal ekstensi dengan perintah

CREATE EXTENSION pg_curl 

Untuk mendapatkan permintaan, buat fungsi

 CREATE OR REPLACE FUNCTION get(url TEXT) RETURNS TEXT LANGUAGE SQL AS $BODY$ WITH s AS (SELECT pg_curl_easy_reset(), --  (  ) pg_curl_easy_setopt('CURLOPT_URL', url), --    pg_curl_header_append('Connection', 'close'), --     pg_curl_easy_perform(), --   pg_curl_easy_getinfo_char('CURLINFO_RESPONSE') --     ) SELECT pg_curl_easy_getinfo_char FROM s; --     $BODY$; 

Untuk permintaan pos urlencoded, buat fungsi

 CREATE OR REPLACE FUNCTION post(url TEXT, request JSON) RETURNS TEXT LANGUAGE SQL AS $BODY$ WITH s AS (SELECT pg_curl_easy_reset(), --  (  ) pg_curl_easy_setopt('CURLOPT_URL', url), --    pg_curl_header_append('Connection', 'close'), --     pg_curl_easy_setopt('CURLOPT_COPYPOSTFIELDS', ( --    WITH s AS ( SELECT (json_each_text(request)).* --    json- ) SELECT array_to_string(array_agg(concat_ws('=', --    urlencoded pg_curl_easy_escape(key), pg_curl_easy_escape(value) )), '&') FROM s )), pg_curl_easy_perform(), --   pg_curl_easy_getinfo_char('CURLINFO_RESPONSE') --     ) SELECT pg_curl_easy_getinfo_char FROM s; --     $BODY$; 

Untuk permintaan posting json, buat fungsi

 CREATE OR REPLACE FUNCTION post(url TEXT, request JSON) RETURNS TEXT LANGUAGE SQL AS $BODY$ WITH s AS (SELECT pg_curl_easy_reset(), --  (  ) pg_curl_easy_setopt('CURLOPT_URL', url), --    pg_curl_header_append('Content-Type', 'application/json; charset=utf-8'), --    pg_curl_header_append('Connection', 'close'), --     pg_curl_easy_setopt('CURLOPT_COPYPOSTFIELDS', request::TEXT), --    pg_curl_easy_perform(), --   pg_curl_easy_getinfo_char('CURLINFO_RESPONSE') --     ) SELECT pg_curl_easy_getinfo_char FROM s; --     $BODY$; 

Untuk mengirim email, buat fungsi

 CREATE OR REPLACE FUNCTION email(url TEXT, username TEXT, password TEXT, subject TEXT, "from" TEXT, "to" TEXT[], data TEXT, type TEXT) RETURNS TEXT LANGUAGE SQL AS $BODY$ WITH s AS (SELECT pg_curl_easy_reset(), --  (  ) pg_curl_easy_setopt('CURLOPT_URL', url), --    pg_curl_easy_setopt('CURLOPT_USERNAME', username), --   pg_curl_easy_setopt('CURLOPT_PASSWORD', password), --   pg_curl_recipient_append("to"), --   pg_curl_header_append('Subject', subject), --   pg_curl_header_append('From', "from"), --   pg_curl_header_append('To', "to"), --   pg_curl_mime_data(data, type:=type), --   pg_curl_header_append('Connection', 'close'), --    pg_curl_easy_perform(), --  pg_curl_easy_getinfo_char('CURLINFO_HEADERS') --   ) SELECT pg_curl_easy_getinfo_char FROM s; --   $BODY$; 

Dan semua ini dapat dilakukan secara tidak sinkron di latar belakang menggunakan penjadwal .