Blog Archives
Foreign Data Wrappers with PostgreSQL and PHP
PostgreSQL is more than a relational database. It has many cool features. Today we’re going to play with Foreign Data Wrappers (FDW). The idea is crate a virtual table from an external datasource and use it like we use a traditional table.
Let me show you an example. Imagine that we’ve got a REST datasource on port 8888. We’re going to use this Silex application, for example
use Silex\Application; $app = new Application(); $app->get('/', function(Application $app) { return $app->json([ ['name' => 'Peter', 'surname' => 'Parker'], ['name' => 'Clark', 'surname' => 'Kent'], ['name' => 'Bruce', 'surname' => 'Wayne'], ]); }); $app->run();
We want to use this datasource in PostgreSQL, so we need to use a “www foreign data wrapper”.
First we create the extension (maybe we need to compile the extension. We can follow the installation instructions here)
CREATE EXTENSION www_fdw;
Now with the extension we need to create a “server”. This server is just a proxy that connects to the real Rest service
CREATE SERVER myRestServer FOREIGN DATA WRAPPER www_fdw OPTIONS (uri 'http://localhost:8888');
Now we need to map our user to the server
CREATE USER MAPPING FOR gonzalo SERVER myRestServer;
And finally we only need our “Foreign table”
CREATE FOREIGN TABLE myRest ( name text, surname text ) SERVER myRestServer;
Now we can perform SQL queries using our Foreign table
SELECT * FROM myRest
We must take care with one thing. We can use WHERE clauses but if we run
SELECT * FROM myRest WHERE name='Peter'
We’ll that the output is the same than “SELECT * FROM myRest”. That’s because if we want to filter something with WHERE clause within Foreign we need to do it in the remote service. WHERE name=‘Peter’ means that our Database will execute the following request:
http://localhost:8888?name=Peter
And we need to handle this parameter. For example doing something like that
use Silex\Application; use Symfony\Component\HttpFoundation\Request; $app = new Application(); $app->get('/', function(Application $app, Request $request) { $name = $request->get('name'); $data = [ ['name' => 'Peter', 'surname' => 'Parker'], ['name' => 'Clark', 'surname' => 'Kent'], ['name' => 'Bruce', 'surname' => 'Wayne'], ]; return $app->json(array_filter($data, function($reg) use($name){ return $name ? $reg['name'] == $name : true; })); }); $app->run();
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.