REST webservice as a Foreign Data Wrapper in postgresql

Question

I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find any resources on how (or if it's possible at all) to embed the XML output of a REST API in a foreign table.

Anyone know if this is possible and if so has any leads on how to implement it?

Answer 1

I actually had a similar question and found a simple solution that worked for me. I took Multicorn and wrote a couple of lines of python in order to import a REST API:

Given a REST API

curl  -H "authentication:ZQzBedExV8YGEztzUJdfqe0nsGNGdstZ" -H "Content-Type: ap/json;charset=UTF-8"  -X POST  http://127.0.0.1:3000/system_module/list.json  -d '{}'

---

I wrote the following simple class:

from . import ForeignDataWrapper
from .utils import log_to_postgres
from logging import INFO
import json
import urllib2
def anubis(apikey):
    req = urllib2.Request('http://127.0.0.1:3000/system_module/list.json')
    req.add_header('apikey', apikey)
    req.add_header('Content-Type', 'application/json;charset=UTF-8')
    resp = urllib2.urlopen(req)
    results = resp.read()
    results = json.loads(results)

    items = results['items']
    for item in items:
        yield {'name': item['name'].encode("utf-8"), 
               'description': item["description"].encode("utf-8"),
               'apikey':apikey}
class AnubisFdw(ForeignDataWrapper):
    def execute(self, quals, columns):
        if not quals:
            return (None, None, None)
        for qual in quals:
            if qual.field_name == "apikey" or qual.operator == "=":
                return anubis(qual.value)

1. Download Multicorn http://multicorn.org

2. have a look in the subdirectory python/multicorn

3. In my case a had to retrieve the data form a REST API returning JSON arrays. Thus I had to parse the josn data returned by the REST API. But in your case you'll have to parse your XML data which should not be a big deal in python.

Best Regards

Babak