http: RESTful Client

The http extension allows you to call RESTful endpoints within Postgres.

Quick demo

Overview

Let's cover some basic concepts:

• REST: stands for REpresentational State Transfer. It's simply a way to request data from external services.
• RESTful APIs are servers which accept HTTP "calls". The calls are typically:
  • GET − Read only access to a resource.
  • POST − Creates a new resource.
  • DELETE − Removes a resource.
  • PUT − Updates an existing resource or creates a new resource.

You can use the http extension to make these network requests from Postgres.

Usage

Enable the extension

Dashboard

1. Go to the Database page in the Dashboard.
2. Click on Extensions in the sidebar.
3. Search for "http" and enable the extension.

SQL

-- Example: enable the "http" extension 
create extension http with schema extensions;

-- Example: disable the "http" extension 
drop extension if exists http;

Even though the SQL code is create extension, this is the equivalent of "enabling the extension". To disable an extension, call drop extension.

It's good practice to create the extension within a separate schema (like extensions) to keep your database clean.

Available functions

While the main usage is simply http('http_request'), there are 5 wrapper functions for specific functionality:

• http_get()
• http_post()
• http_put()
• http_delete()
• http_head()

Returned values

A successful call to a web URL from the http extension returns a record with the following fields:

• status: integer
• content_type: character varying
• headers: http_header[]
• content: character varying. Typically you would want to cast this to jsonb using the format content::jsonb

Examples

Simple GET example

select 
  "status", "content"::jsonb
from 
  http_get('https://jsonplaceholder.typicode.com/todos/1');

Simple POST example

select 
  "status", "content"::jsonb
from 
  http_post(
    'https://jsonplaceholder.typicode.com/posts',
    '{ "title": "foo", "body": "bar", "userId": 1 }',
    'application/json'
  );

Resources

• Official http GitHub Repository