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.
content::json->'url' as url,
content::json->'title' as title
This would fetch the
url to the a table as below:
url | title
"https://imgs.xkcd.com/comics/zeppelin.jpg" | "A tribute to Buttercup Festival"
Well, the possbilities are immense with this API, we could easily do invoke all HTTP verbs!
CREATE DOMAIN http_method AS text
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:
apt install make gcc g++ postgresql-server-dev-13 libcurl4-openssl-dev
tar xvfz v1.3.1.tar.gz
make && make install
/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 DATABASE 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.