Try pgsql-http
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.
Read other posts