5

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?

  • 1
    At this point it's really a matter of reading the existing FDW implementations, the API and the documentation. You may find it significantly easier to implement the wrapper using set-returning functions in pl/pythonu, pl/perlu, etc rather than trying to write an FDW. Take a look at the Redis FDW Dave Page has been working on: github.com/dpage/redis_fdw and the related blog posts. – Craig Ringer Mar 27 '13 at 11:31
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.