Postgres has been my default choice of DB for most of my applications these days and recently I came across an HTTP client for PostgreSQL—retrieve a web page from inside the database! Without more theory, here is a simple example of how useful this can be.

Example: Fetch Random XKCD Comic

Fetch random comics from XKCD is my version of “Hello World”. Here is a simple SQL query to fetch a random XKCD comic from a service I had written and hosted on Heroku:

SELECT 
  content::json->'url' as url, 
  content::json->'title' as title 
FROM http_get('https://xkcd-imgs.herokuapp.com/');

This would fetch the title and url to a table as below:

             url                             |               title                
---------------------------------------------+-----------------------------------
 "https://imgs.xkcd.com/comics/zeppelin.jpg" | "A tribute to Buttercup Festival"
(1 row)

HTTP Verbs Support

The possibilities are immense with this API, we could easily invoke all HTTP verbs!

CREATE 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'
);

Setting it Up on a GNU/Linux Machine

Install Dependencies

# install the basic deps
apt install make gcc g++ postgresql-server-dev-13 libcurl4-openssl-dev
# 13 or your version of pg server

### Fetch, Extract, Make and Install
bash
wget https://github.com/pramsey/pgsql-http/archive/refs/tags/v1.3.1.tar.gz
tar xvfz v1.3.1.tar.gz
cd pgsql-http-1.3.1
make && make install

### Install the Extension
bash
/usr/bin/install -c -m 644 ./http--1.3.sql ./http--1.2--1.3.sql ./http--1.1--1.2.sql ./http--1.0--1.1.sql '/usr/share/postgresql/11/extension/'

### Create the Extension in Your Database
sql
su postgres
psql

CREATE DATABASE http_foo;
\connect http_foo;

CREATE SCHEMA contrib;
GRANT USAGE ON SCHEMA contrib TO some_user_group;
ALTER DATABASE http_foo SET search_path=public,contrib;

CREATE EXTENSION http SCHEMA contrib;

Now you should be able to use http_get and similar functions in your query!