Calling RESTful Web Services from PostgreSQL procedure/function

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 DEFINERAS $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$

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 plpython2uAS $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 plpython2uAS $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$;

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.