HTTP Request in Postgres

Postgres had been my default choice of DB for most of my application these days and recent I came across a [HTTP client] for PostgreSQL, retrieve a web page from inside the database, so without more theory, here is a simple example on how useful this can be!

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 the a table as below:


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

Well, the possbilities are immense with this API, we could easily do 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 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
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
/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/'
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 likes in your query!

Feel free to share this article. You may as well ping me on Twitter.

Published