Hi,
I am using a SQL function in postgres RDS which make http call, However we are unbale to implement it due to following reasons:
We are trying to install this extension which are use to make api call via postgres itself
https://github.com/pramsey/pgsql-http/tree/master
But I have to clone this repo in RDS instance and then I can use it but I cannot do that as RDS not allowed to access of OS files.
Also I have tried pg_tle extension which RDS provide for installing extension but in this extesnion we are using C langauge which also RDS does not allow.
So is there any extesnion which RDS provide for caling http api call in postgres or there is any workaround we can do it to install this extension over our RDS cluster.
Thank you.
SELECT pgtle.install_extension
(
'http',
'0.1',
'HTTP client for PostgreSQL, retrieve a web page from inside the database.',
$_pg_tle_$
CREATE OR REPLACE DOMAIN http_method AS text
CHECK (
VALUE ILIKE 'get' OR
VALUE ILIKE 'post' OR
VALUE ILIKE 'put' OR
VALUE ILIKE 'delete' OR
VALUE ILIKE 'patch' OR
VALUE ILIKE 'head'
);
CREATE OR REPLACE DOMAIN content_type AS text
CHECK (
VALUE ~ '^\S+\/\S+'
);
CREATE OR REPLACE TYPE http_header AS (
field VARCHAR,
value VARCHAR
);
CREATE OR REPLACE TYPE http_request AS (
method http_method,
uri VARCHAR,
headers http_header[],
content_type VARCHAR,
content VARCHAR
);
CREATE OR REPLACE TYPE http_response AS (
status INTEGER,
content_type VARCHAR,
headers http_header[],
content VARCHAR
);
CREATE OR REPLACE FUNCTION http_header(field VARCHAR, value VARCHAR)
RETURNS http_header
AS $$ SELECT $1, $2 $$
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION http(request @extschema@.http_request)
RETURNS http_response
AS 'MODULE_PATHNAME', 'http_request'
LANGUAGE 'c';
CREATE OR REPLACE FUNCTION http_get(uri VARCHAR)
RETURNS http_response
AS $$ SELECT @extschema@.http(('GET', $1, NULL, NULL, NULL)::http_request) $$
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION http_post(uri VARCHAR, content VARCHAR, content_type VARCHAR)
RETURNS http_response
AS $$ SELECT @extschema@.http(('POST', $1, NULL, $3, $2)::http_request) $$
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION http_put(uri VARCHAR, content VARCHAR, content_type VARCHAR)
RETURNS http_response
AS $$ SELECT @extschema@.http(('PUT', $1, NULL, $3, $2)::http_request) $$
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION http_patch(uri VARCHAR, content VARCHAR, content_type VARCHAR)
RETURNS http_response
AS $$ SELECT @extschema@.http(('PATCH', $1, NULL, $3, $2)::http_request) $$
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION http_delete(uri VARCHAR)
RETURNS http_response
AS $$ SELECT @extschema@.http(('DELETE', $1, NULL, NULL, NULL)::http_request) $$
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION urlencode(string VARCHAR)
RETURNS TEXT
AS 'MODULE_PATHNAME'
LANGUAGE 'c'
IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION http_head(uri VARCHAR)
RETURNS http_response
AS $$ SELECT @extschema@.http(('HEAD', $1, NULL, NULL, NULL)::http_request) $$
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION http_set_curlopt(curlopt VARCHAR, value VARCHAR)
RETURNS boolean
AS 'MODULE_PATHNAME', 'http_set_curlopt'
LANGUAGE 'c';
CREATE OR REPLACE FUNCTION http_reset_curlopt()
RETURNS boolean
AS 'MODULE_PATHNAME', 'http_reset_curlopt'
LANGUAGE 'c';
CREATE OR REPLACE FUNCTION http_list_curlopt()
RETURNS TABLE(curlopt text, value text)
AS 'MODULE_PATHNAME', 'http_list_curlopt'
LANGUAGE 'c';
CREATE OR REPLACE FUNCTION urlencode(string BYTEA)
RETURNS TEXT
AS 'MODULE_PATHNAME'
LANGUAGE 'c'
IMMUTABLE STRICT;
CREATE FUNCTION OR REPLACE urlencode(data JSONB)
RETURNS TEXT
AS 'MODULE_PATHNAME', 'urlencode_jsonb'
LANGUAGE 'c'
IMMUTABLE STRICT;
CREATE FUNCTION OR REPLACE http_get(uri VARCHAR, data JSONB)
RETURNS http_response
AS $$
SELECT @extschema@.http(('GET', $1 || '?' || @extschema@.urlencode($2), NULL, NULL, NULL)::@extschema@.http_request)
$$
LANGUAGE 'sql';
CREATE FUNCTION OR REPLACE http_post(uri VARCHAR, data JSONB)
RETURNS http_response
AS $$
SELECT @extschema@.http(('POST', $1, NULL, 'application/x-www-form-urlencoded', @extschema@.urlencode($2))::@extschema@.http_request)
$$
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION http_delete(uri VARCHAR, content VARCHAR, content_type VARCHAR)
RETURNS http_response
AS $$ SELECT @extschema@.http(('DELETE', $1, NULL, $3, $2)::@extschema@.http_request) $$
LANGUAGE 'sql';
CREATE FUNCTION text_to_bytea(data TEXT)
RETURNS BYTEA
AS 'MODULE_PATHNAME', 'text_to_bytea'
LANGUAGE 'c'
IMMUTABLE STRICT;
CREATE FUNCTION bytea_to_text(data BYTEA)
RETURNS TEXT
AS 'MODULE_PATHNAME', 'bytea_to_text'
LANGUAGE 'c'
IMMUTABLE STRICT;
$_pg_tle_$
);