Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Documentation

Regina Obe edited this page · 8 revisions
Clone this wiki locally

Provides ability to work with data from web services using SQL.

Benefits

  • ability to select data from web service using different criteria (filters, sort, aggregation, other sql features);
  • ability to join/union data from web service with other table data (probably output of another web service);
  • export of data from web service into table view;
  • common interface for any web service.

Details

Implemented using Foreign data wrappers. (SQL/MED)

Main complexity here is to build easy interface for wide range of web services. Main goal for this implementation is RESTfull services.

Configuration

Each FDW will have following configuration options:

  • uri - base URI for web service;
  • uri_select - part of URI for selection command, default - "";
  • uri_insert - part of URI for insert command, default - "" (reserved for future);
  • uri_delete - part of URI for delete command, default - "" (reserved for future);
  • uri_update - part of URI for update command, default - "" (reserved for future);
  • method_select - http method for select command, default value - "GET";
  • method_insert - http method for insert command, default value - "PUT";
  • method_delete - http method for delete command, default value - "DELETE";
  • method_update - http method for update command, default value - "POST";
  • request_user_agent - "User-Agent" http header to use, default "www_fdw postgres extension";
  • request_serialize_callback - callback for forming request (for non standard cases, will be covered later);
  • request_serialize_type - type which shows how quals are passed to the request_serialize_callback, possible value:
    • log - default, same serialization as with debug_print_parse option;
    • null - doesn't specify it and pass as null value;
    • json - tree is serialized into json string;
    • xml - tree is serialized into xml string;
  • request_serialize_human_readable - formatted/intended quals, possible value:
    • 0 - no;
    • 1 - yes;
  • response_type - web service response content type, possible values:
    • json - default value;
    • xml;
    • other;
  • response_deserialize_callback - callback for parsing responses of "other" type;
  • response_iterate_callback - callback for extracting/iterating rows from response.

  • ssl_cert - corresponds to CURLOPT_SSLCERT

  • ssl_key - corresponds to CURLOPT_SSLKEY
  • cainfo - used to specify a certificate authority bundle file (crt file path) CURLOPT_CAINFO
  • proxy - proxy server to use instead of going direct CURLOPT_PROXY

Commands

There is following correspondence between sql commands and http methods:

  • SELECT - GET
  • INSERT - PUT
  • DELETE - DELETE
  • UPDATE - POST

There are configurable options for correspondence above.

In the current version (postgres 9.1) there is no implementation for FDW INSERT/DELETE/UPDATE. But stuff related for INSERT/DELETE/UPDATE is reserved for future.

Request

Since all request parameters to web service are pairs (key,value), therefore default behavior for request preparation is:

pack pairs (column, value) from sql query as "column=pair". Operators differ from "=" will raise an error (if request_serialize_callback wasn't specified).

request_serialize_callback is designed for non standard request preparation (aliases for parameters, default parameters etc). Also it gives a way to form url string or specify post data.

Response

If response_deserialize_callback is specialized it will be called. It's return value is array of result rows.

Other way if response type is one of:

  • json
  • xml

response will be auto parsed. First array (with all elements of the same type/structure) will be returned (breadth-first search).

For other response type there is no auto parsing and response_deserialize_callback has to be specialized.

If response_iterate_callback is specified it will be called from IterateForeignScan function with current tuple.

Callbacks

Example:

CREATE OR REPLACE FUNCTION test_response_deserialize_callback(options WWWFdwOptions, response xml)
    RETURNS SETOF www_fdw_test AS $$
DECLARE
    rows RECORD;
    title text;
    link text;
    snippet text;
    r RECORD;
BEGIN
    RAISE NOTICE 'options parameter: %', options;
    RAISE NOTICE 'response parameter: %', response;
    FOR rows IN SELECT unnest(xpath('/doc/rows/row', response)) LOOP
        title := (xpath('/row/title/text()', rows.unnest))[1];
        link := (xpath('/row/link/text()', rows.unnest))[1];
        snippet := (xpath('/row/snippet/text()', rows.unnest))[1];
        r := ROW(title, link, snippet);
        RETURN NEXT r;
    END LOOP;
END; $$ LANGUAGE PLPGSQL;

Callback function name is passed as FDW option:

CREATE SERVER www_server FOREIGN DATA WRAPPER www_fdw OPTIONS
    (response_iterate_callback 'example_response_iterate_callback');

Call to this function is implemented through SPI.

More callback examples can be found at Examples.

Examples

For more real examples check Examples.

Extension installation:

-- Install the extension
CREATE EXTENSION www_fdw;

-- Create the foreign server, a pointer to the web service.
CREATE SERVER www_service FOREIGN DATA WRAPPER www_fdw 
    OPTIONS (uri 'URI', ...OTHER_PARAMETERS_HERE...);

CREATE USER MAPPING FOR current_user SERVER www_service;

Example for Google search API:

CREATE SERVER www_fdw_google_search_server FOREIGN DATA WRAPPER www_fdw 
    OPTIONS (uri 'https://ajax.googleapis.com/ajax/services/search/web?v=1.0');

CREATE USER MAPPING FOR current_user SERVER www_fdw_google_search_server;

CREATE FOREIGN TABLE www_fdw_google_search (
  title text,
  link text,
  snippet text,
  q text /* used for easy query specifying */
) SERVER www_fdw_google_search_server;

Queries examples:

select title,snippet from www_fdw_google_search where q=’cat dog’ limit 1;
    title                                  |             snippet
-------------------------------------------+----------------------------------------------
 CatDog - Wikipedia, the free encyclopedia | CatDog is an American animated television ...

-- example of union with full text search:
select * from documents where body @@ plainto_tsquery('cat dog')
union
select title,snippet from www_fdw_google_search where q=’cat dog’ limit 1;
    name                                   |             body
-------------------------------------------+----------------------------------------------
 cat and dog                               | cat and dog set on a pavement
 CatDog - Wikipedia, the free encyclopedia | CatDog is an American animated television ...

Other interesting examples:

More

Besides providing specialized callbacks there is another way: building web service "in the middle", which translates formats. In simplest case it implements GET returning json array with row objects.

Central place is realization of extension core, which will be developed & QAed independently from any callback/web_services implementations. In this case it will be really easy to reuse this extension with minimum of coding for new services. From the other side, any complicated service can be handled with more complex callbacks.

Something went wrong with that request. Please try again.