Building one HTTP client in PostgreSQL with PL/Python


Don’t ask me way, but I need to call to a HTTP server (one Silex application) from a PostgreSQL database.

I want to do something like this:

select get('http://localhost:8080?name=Gonzalo')->'hello';

PostgreSQL has a datatype for json. It’s really cool and it allows us to connect our HTTP server and our SQL database using same datatype.

PostgreSQL also allows us to create stored procedures using different languages. The default language is PL/pgSQL. PL/pgSQL is a simple language where we can embed SQL. But we also can use Python. With Python we can easily create HTTP clients, for example with urllib2. That means that develop our a HTTP client for a PostgreSQL database is pretty straightforward.

CREATE OR REPLACE FUNCTION get(uri character varying)
  RETURNS json AS
$BODY$
import urllib2

data = urllib2.urlopen(uri)

return data.read()

$BODY$
  LANGUAGE plpython2u VOLATILE
  COST 100;
ALTER FUNCTION get(character varying)
  OWNER TO gonzalo;

Ok that’s a GET client, but we also want a POST client to do something like this:

select post('http://localhost:8080', '{"name": "Gonzalo"}'::json)->'hello';

As you can see I want to use application/json instead of application/x-www-form-urlencoded to send request parameters. I wrote about it here time ago. So I will create one endpoint within my Silex server to handle my POST requests to:

<?php
include __DIR__ . '/../vendor/autoload.php';

use Silex\Application;
use Symfony\Component\HttpFoundation\Request;
use G\AngularPostRequestServiceProvider;

$app = new Application(['debug' => true]);

$app->register(new AngularPostRequestServiceProvider());

$app->post('/', function (Application $app, Request $request) {
    return $app->json(['hello' => $request->get('name')]);
});

$app->get('/', function (Application $app, Request $request) {
    return $app->json(['hello' => $request->get('name')]);
});

$app->run();

And now we only need to create one stored procedure to send POST requests

CREATE OR REPLACE FUNCTION post(
    uri character varying,
    paramenters json)
  RETURNS json AS
$BODY$
import urllib2

clen = len(paramenters)
req = urllib2.Request(uri, paramenters, {'Content-Type': 'application/json', 'Content-Length': clen})
f = urllib2.urlopen(req)
return f.read()

$BODY$
  LANGUAGE plpython2u VOLATILE
  COST 100;
ALTER FUNCTION post(character varying, json)
  OWNER TO gonzalo;

And that’s all. At least this simple script is exactly what I need.

About Gonzalo Ayuso

Web Architect. PHP, Python, Node, Angular, ionic, PostgreSQL, Linux, ... Always learning.

Posted on September 21, 2015, in databases, php, PostgreSQL, Python, silex, Technology and tagged , , , , , . Bookmark the permalink. 3 Comments.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: