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();

About Gonzalo Ayuso

Web Architect specialized in Open Source technologies. PHP, Python, JQuery, Dojo, PostgreSQL, CouchDB and node.js but always learning.

Posted on February 22, 2016, in php, PostgreSQL, Technology and tagged , , , , . Bookmark the permalink. 4 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

Follow

Get every new post delivered to your Inbox.

Join 1,141 other followers

%d bloggers like this: