REST webservice as a Foreign Data Wrapper in postgresql

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?

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)


-
Download Multicorn http://multicorn.org

-
have a look in the subdirectory python/multicorn

-
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

StackExchange Database Administrators Q#37664, answer score: 5