Calling RESTful Web Services from PostgreSQL procedure/function

user_3169868 0
Viewed 24488

I have been provided RESTful web services to push data into a remote DB of another application. I need to call these services to push data from PostgreSQL DB by sending data in JSON format as GET/POST parameters to the web service. Is it possible to call these web services from the PostgreSQL functions (periodically) which push data into my database in the first place, or write JAVA code to call these web services that run queries on PostgreSQL database and call web services to pass them to the remote DB.

rest web-services postgresql
3 Answers

Yes, it is possible, althought not directly from Postgresql itself. I don't know about Java but the fastest way is to use plperlu with REST::Client package, e.g.:

CREATE OR REPLACE FUNCTION restful.put(auri character varying, ajson_text text)
 RETURNS text
 LANGUAGE plperlu
 SECURITY DEFINER
AS $function$
  use REST::Client;  
  use Encode qw(encode);
  my $client = REST::Client->new();    
  $client->getUseragent()->proxy( 'https', 'http://some-proxy/' ); # use for proxy authentication
  $client->addHeader('Content-Type', 'application/json');          # headers
  $client->POST( $_[0], encode('UTF-8', $_[1]));                   # encoding
  return $client->responseContent();  
$function$
user_6445110

Using plpython2u language:

Solution 1: (using urllib2)

CREATE OR REPLACE FUNCTION public.py_pgrest(uri text, body text DEFAULT NULL::text, content_type text DEFAULT 'application/json'::text)
 RETURNS text
 LANGUAGE plpython2u
AS $function$
    import urllib2
    from urllib2 import Request, urlopen, URLError, HTTPError
    req = Request(uri)
    if body:
        req.add_data(body)
    if content_type:
        req.add_header('Content-Type', content_type)
    try:
        data = urlopen(req)
    except HTTPError as e:
        return e
    except URLError as e:
        if hasattr(e, 'reason'):
            return e.reason
        elif hasattr(e, 'code'):
            return e.code
        else:
            return e
    else:
        return data.read()
$function$
;

Solution 2: (using requests)

CREATE OR REPLACE FUNCTION public.py_pgrest(p_url text, p_method text DEFAULT 'GET'::text, p_data text DEFAULT ''::text, p_headers text DEFAULT '{"Content-Type": "application/json"}'::text)
 RETURNS text
 LANGUAGE plpython2u
AS $function$
    import requests, json
    try:
        r = requests.request(method=p_method, url=p_url, data=p_data, headers=json.loads(p_headers))
    except Exception as e:
        return e
    else:
        return r.content
$function$
;
user_80694900

Solution 1: Using JOOQ

First you generate classes from xml file to match the postgresql database structure.

Then you can use them in your code, for example in your Java application:

try (DatabaseContext dbCtx=new DatabaseContext())
    {

      DSLContext ctx=dbCtx.getContext();
      ctx.insertInto(TABLE)
      .set(TABLE.ID, values.getId())
      .set(TABLE.DATA, values.getData().toString())
      .execute();
    }

Solution 2: Using Hibernate

You first setup hibernate xml and Java files for mapping your database similar to the JOOQ solution.

Then you again connect to database and push data.

  Configuration cfg=new Configuration();  
  cfg.configure("your_database_configuration_file.cfg.xml");
  SessionFactory factory=cfg.buildSessionFactory();  
  Session session=factory.openSession();  
  Transaction t=session.beginTransaction();  

  TranstateData e1=new TranstateData();  
  e1.setId(idR);  
  e1.setData(dataR);  
  session.persist(e1);
  t.commit();
  session.close();

This solutions are better than writing SQL to connect to the database directly because you can just update from time to time (eg. write a task that does that) when the tables are modified (meaning you do not have to write the configurations twice! the code will automatically generate the changes). You also find errors faster because you see the classes you have and variables. Not to mention they are not the only ways to play around with JOOQ and Hibernate... also other possibilities are available. The internet is full of other examples. MyBatis is also a solution (Solution 3). All mentioned are supported for PostgreSQL.

user_37562080
Related