This LightDB extension implements http requests.
If you first install, use CREATE EXTENSION http
.
If you already installed a previous version and you just want to upgrade, then ALTER EXTENSION http UPDATE
.
Every HTTP call is a made up of an http_request
and an http_response
.
Composite type "public.http_request" Column | Type | Modifiers --------------+-------------------+----------- method | http_method | uri | character varying | headers | http_header[] | content_type | character varying | content | character varying | Composite type "public.http_response" Column | Type | Modifiers --------------+-------------------+----------- status | integer | content_type | character varying | headers | http_header[] | content | character varying |
The utility functions, http_get()
, http_post()
, http_put()
, http_delete()
and http_head()
are just wrappers around a master function, http(http_request)
that returns http_response
.
The headers
field for requests and response is a LightDB array of type http_header
which is just a simple tuple.
Composite type "public.http_header" Column | Type | Modifiers --------+-------------------+----------- field | character varying | value | character varying |
As seen in the examples, you can unspool the array of http_header
tuples into a result set using the LightDB unnest()
function on the array. From there you select out the particular header you are interested in.
http_header(field VARCHAR, value VARCHAR)
returns http_header
http(request http_request)
returns http_response
http_get(uri VARCHAR)
returns http_response
http_get(uri VARCHAR, data JSONB)
returns http_response
http_post(uri VARCHAR, content VARCHAR, content_type VARCHAR)
returns http_response
http_post(uri VARCHAR, data JSONB)
returns http_response
http_put(uri VARCHAR, content VARCHAR, content_type VARCHAR)
returns http_response
http_patch(uri VARCHAR, content VARCHAR, content_type VARCHAR)
returns http_response
http_delete(uri VARCHAR, content VARCHAR, content_type VARCHAR))
returns http_response
http_head(uri VARCHAR)
returns http_response
http_set_curlopt(curlopt VARCHAR, value varchar)
returns boolean
http_reset_curlopt()
returns boolean
http_list_curlopt()
returns setof(curlopt text, value text)
urlencode(string VARCHAR)
returns text
urlencode(data JSONB)
returns text
Select CURL options are available to set using the http_set_curlopt(curlopt VARCHAR, value varchar)
function.
The http_reset_curlopt()
approach described above is recommended. The global variables below will be deprecated and removed over time.
By default each request uses a fresh connection and assures that the connection is closed when the request is done. This behavior reduces the chance of consuming system resources (sockets) as the extension runs over extended periods of time.
High-performance applications may wish to enable keep-alive and connection persistence to reduce latency and enhance throughput. The following GUC variable changes the behavior of the http extension to maintain connections as long as possible:
http.keepalive = 'on'
By default a 60 second timeout is set for the completion of a request. If a different timeout is desired the following GUC variable can be used to set it in milliseconds:
http.timeout_msec = 60000
-- URL encode a string. SELECT urlencode('my special string''s and things?'); urlencode ------------------------------------- my+special+string%27s+and+things%3F (1 row) -- URL encode a JSON associative array. SELECT urlencode(jsonb_build_object('name','Colin and James')); urlencode ------------------------------------- name=Colin+and+James (1 row) -- Run a GET request and see the content. SELECT content FROM http_get('http://httpbin.org/ip'); content ----------------------------- {"origin":"24.69.186.43"} + (1 row) -- Run a GET request with an Authorization header. SELECT content::json->'headers'->>'Authorization' FROM http(( 'GET', 'http://httpbin.org/headers', ARRAY[http_header('Authorization','Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9')], NULL, NULL )::http_request); content ---------------------------------------------- Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9 (1 row) -- Read thestatus
andcontent
fields out of ahttp_response
object. SELECT status, content_type FROM http_get('http://httpbin.org/'); status | content_type --------+-------------------------- 200 | text/html; charset=utf-8 (1 row) -- Show all thehttp_header
in anhttp_response
object. SELECT (unnest(headers)).* FROM http_get('http://httpbin.org/'); field | value ----------------------------------+------------------------------- Connection | close Server | meinheld/0.6.1 Date | Tue, 09 Jan 2018 18:40:30 GMT Content-Type | text/html; charset=utf-8 Content-Length | 13011 Access-Control-Allow-Origin | * Access-Control-Allow-Credentials | true X-Powered-By | Flask X-Processed-Time | 0.0208520889282 Via | 1.1 vegur -- Use the PUT command to send a simple text document to a server. SELECT status, content_type, content::json->>'data' AS data FROM http_put('http://httpbin.org/put', 'some text', 'text/plain'); status | content_type | data --------+------------------+----------- 200 | application/json | some text -- Use the PATCH command to send a simple JSON document to a server. SELECT status, content_type, content::json->>'data' AS data FROM http_patch('http://httpbin.org/patch', '{"this":"that"}', 'application/json'); status | content_type | data --------+------------------+------------------ 200 | application/json | '{"this":"that"}' -- Use the DELETE command to request resource deletion. SELECT status, content_type, content::json->>'url' AS url FROM http_delete('http://httpbin.org/delete'); status | content_type | url --------+------------------+--------------------------- 200 | application/json | http://httpbin.org/delete -- As a shortcut to send data to a GET request, pass a JSONB data argument. SELECT status, content::json->'args' AS args FROM http_get('http://httpbin.org/get', jsonb_build_object('myvar','myval','foo','bar')); -- To POST to a URL using a data payload instead of parameters embedded in the URL, encode the data in a JSONB as a data payload. SELECT status, content::json->'form' AS form FROM http_post('http://httpbin.org/post', jsonb_build_object('myvar','myval','foo','bar')); -- To access binary content, you must coerce the content from the defaultvarchar
representation to abytea
representation using thetextsend
function. Using the defaultvarchar::bytea
cast will not work, as the cast will stop the first time it hits a zero-valued byte (common in binary data). WITH http AS ( SELECT * FROM http_get('http://httpbin.org/image/png') ), headers AS ( SELECT (unnest(headers)).* FROM http ) SELECT http.content_type, length(textsend(http.content)) AS length_binary, headers.value AS length_headers FROM http, headers WHERE field = 'Content-Length'; content_type | length_binary | length_headers --------------+---------------+---------------- image/png | 8090 | 8090