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.
Step 2: Click on the “Install fdw_www” link for the target database.
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'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.