Hello, I tried to use pramsey/pgsql-http for retrieve a web page from inside the database. GitHub

Environments

  • Postgresql 11
  • Ubuntu 18.04.3 LTS (container)
  • Docker Desktop 2.1.0.3

Step 1: into a docker container

My environment is macOS, so I uses docker

$ docker run --rm -it ubuntu bash
root@2cb4c192b274:/#

Step 2: installing base libraries

root@2cb4c192b274:/# apt update && apt install -y build-essential git

Step 3: installing postgres

root@2cb4c192b274:/# apt install -y postgresql-11

Step 4: build

cd /tmp; git clone https://github.com/pramsey/pgsql-http.git; cd pgsql-http

But, It failed.

root@2cb4c192b274:/tmp/pgsql-http#  make
make: curl-config: Command not found
You need to install postgresql-server-dev-NN for building a server-side extension or libpq-dev for building a client-side application.
make: *** No targets.  Stop.

Step 5: install required libraries

root@2cb4c192b274:/tmp/pgsql-http# apt install -y libcurl4-openssl-dev
root@2cb4c192b274:/tmp/pgsql-http# apt install -y libpq-dev postgresql-server-dev-all

Step 6: Re:Build

root@2cb4c192b274:/tmp/pgsql-http# make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -I. -I./ -I/usr/include/postgresql/11/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/mit-krb5  -c -o http.o http.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -shared -o http.so http.o -L/usr/lib/x86_64-linux-gnu  -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-6.0/lib  -L/usr/lib/x86_64-linux-gnu/mit-krb5 -Wl,--as-needed  -lcurl
/usr/bin/clang-6.0 -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I. -I./ -I/usr/include/postgresql/11/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/mit-krb5 -flto=thin -emit-llvm -c -o http.bc http.c

root@2cb4c192b274:/tmp/pgsql-http# make install
/bin/mkdir -p '/usr/lib/postgresql/11/lib'
/bin/mkdir -p '/usr/share/postgresql/11/extension'
/bin/mkdir -p '/usr/share/postgresql/11/extension'
/usr/bin/install -c -m 755  http.so '/usr/lib/postgresql/11/lib/http.so'
/usr/bin/install -c -m 644 .//http.control '/usr/share/postgresql/11/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/'
/bin/mkdir -p '/usr/lib/postgresql/11/lib/bitcode/http'
/bin/mkdir -p '/usr/lib/postgresql/11/lib/bitcode'/http/
/usr/bin/install -c -m 644 http.bc '/usr/lib/postgresql/11/lib/bitcode'/http/./
cd '/usr/lib/postgresql/11/lib/bitcode' && /usr/lib/llvm-6.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o http.index.bc http/http.bc

root@2cb4c192b274:/tmp/pgsql-http#

very simple!

Step 7: run a postgres server and login

root@2cb4c192b274:/tmp/pgsql-http# /etc/init.d/postgresql start
 * Starting PostgreSQL 11 database server                         [ OK ]

and,

root@2cb4c192b274:/tmp/pgsql-http# su - postgres
postgres@2cb4c192b274:~$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

postgres=#

Step 8: enable extension

postgres=# CREATE EXTENSION http;
CREATE EXTENSION

Step 9: try to retrive a my message in gist

I try to retrive a my message in gist My message

postgres=# SELECT content FROM http_get('https://gist.githubusercontent.com/MizukiSonoko/60e520dffa19c6aaac9831c6d617ee52/raw/a9787c2ace4e4d58dae49edbcfae6403cc3de847/Helllo!');
                     content
--------------------------------------------------
 Hello postgres world! I'm Sonoko Mizuki in Gist!
(1 row)

It’s cool.

Step 10: Try to retrive a message from json in gist

In addition, postgres supports JSON, so I try to retrive a message from json in gist My json file

postgres=# SELECT content::json->>'message' FROM http_get('https://gist.githubusercontent.com/MizukiSonoko/60e520dffa19c6aaac9831c6d617ee52/raw/e0f6f61d7f8a60a9557be20606fd6f15894f2f93/hello_iam_.json');  
                ?column?
----------------------------------------
 Hello postgres world! I'm json message
(1 row)

very cool.