WWW Foreign Data Wrapper

This post will review installing and basic usage of the WWW Foreign Data Wrapper.

We will install the FDW and then create a foreign table and query it.

We have created a database called acugrss_wwwexample.  

Step 1: Log into cPanel and, under Foreign Data Wrappers, click the WWW icon.

install

 

Step 2: Click on the “Install fdw_www” link for the target database.

 

install2

 

Step 3: Connect via SSH to your target database (acugrss_wwwexample) in our case:


acugrss@acugres.com [~]# psql -d acugrss_wwwexample 
Password: psql (9.3.5) Type "help" for help. 
acugrss_wwwexample=>

  
Step 4: Following the example from the docs at https://github.com/cyga/www_fdw/wiki/Documentation, we will create a server using the Google Search API:

 


acugrss_wwwexample=> 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');

This should return “CREATE SERVER”
 
Step 5: Now, we will create a mapping for the user:

CREATE USER MAPPING FOR acugrss SERVER www_fdw_google_search_server;

This should return “CREATE USER MAPPING”
 
Step 6: Finally, we create our foreign table:


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;

 
This should return “CREATE FOREIGN TABLE”
 
We can now query using our foreign table:


select title,snippet from www_fdw_google_search where q='PostgreSQL' limit 3;

Which returns:
 


acugrss_wwwexample=> select title,snippet from www_fdw_google_search where q='PostgreSQL' limit 3;
                                 title                                 | snippet

-----------------------------------------------------------------------+--------
-
 <b>PostgreSQL</b>: The world&#39;s most advanced open source database |
 <b>PostgreSQL</b>: Downloads                                          |
 <b>PostgreSQL</b>: Documentation                                      |
(3 rows)


 

Learn more about www_fdw at https://github.com/cyga/www_fdw/wiki/Documentation

If you need assistance, create a support ticket and we are here to assist.