Database Links - For non-C hackers

Recently PostgreSQL has added support for so called "Foreign Data Wrappers". External data sources can now be included into PostgreSQL and people can access remote tables almost like local tables.

To access a remote data source it is necessary to install a module. Currently there are various different modules for different data sources such as remote PostgreSQL or Oracle databases around. Those modules can be used straight away and more important – easily.

It starts to be a little tricky, however, if you want to present some new webservices or some rare database system as table. In this case you had to write a C module. This is not hard either but it takes considerable time and for non-C programmers it has been an obstacle which is fairly hard to overcome.

Multicorn (http://multicorn.org/) is supposed to fix the problem and provide ordinary people with the ability to write simple Foreign Data Wrappers in some really simple language: Python.

With Multicorn you can write, say, a data wrapper for Google in just 40 lines:

"""
A foreign data wrapper for performing google searches.

"""

from . import ForeignDataWrapper

import json
import urllib

def google(search):
    """Retrieves results from google using the json api"""
    query = urllib.urlencode({'q': search})
    url = ('http://ajax.googleapis.com/ajax/'
           'services/search/web?v=1.0&%s' % query)
    response = urllib.urlopen(url)
    results = response.read()
    results = json.loads(results)
    data = results['responseData']
    hits = data['results']
    for hit in hits:
        yield {'url': hit['url'].encode("utf-8"),
               'title': hit["titleNoFormatting"].encode("utf-8"),
               'search': search.encode("utf-8")}

class GoogleFdw(ForeignDataWrapper):
    """A Google search foreign data wrapper.

    Parses the quals to find anything ressembling a search criteria, and
    returns the google search result for it.
    Available columns are: url, title, search.

    """

    def execute(self, quals, columns):
        if not quals:
            return ("No search specified",)
        for qual in quals:
            if qual.field_name == "search" or qual.operator == "=":
                return google(qual.value)

(code taken from Multicorn source).

Most of the complexity associated with Foreign Data Wrappers is nicely hidden and I found it really really easy to just prototype some interface code to quickly test if an idea is feasible or not before actually sitting down to write the C code.