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 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

<Tabs scrollable size="small" type="underlined" defaultActiveId="dashboard" queryGroup="database-method"

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

-- 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 the public schema clean.

Available functions

While the main usage is 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
  extensions.http_get('https://jsonplaceholder.typicode.com/todos/1');

Simple POST example

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

Resources

• Official http GitHub Repository