Replication and External Data - PostgreSQL: Up and Running - page 185

At this time, the FDW extension automatically installs two wrappers by default:
file_fdw and postgres_fdw . If you need to to wrap foreign data sources, start by visiting
these two links to see whether someone has already done the work of creating wrappers.
If not, try creating one yourself. If you succeed, be sure to share it with others.
In PostgreSQL 9.1 and 9.2, you're limited to SELECT queries against the FDW. Post‐
greSQL 9.3 introduced an API feature to update foreign tables. postgres_fdw is the only
FDW shipped with PostgreSQL that supports this new feature.
In this section, we'll demonstrate how to register foreign servers, foreign users, and
foreign tables, and finally, how to query foreign tables. Although we use SQL to create
and delete objects in our examples, you can perform the exact same commands using
pgAdmin III.
Querying Flat Files
The file_fdw wrapper is packaged as an extension. To install, use the SQL:
CREATE EXTENSION file_fdw ;
Although file_fdw can read only from file paths accessible by your local server, you
still need to define a server for it for the sake of consistency. Issue the following command
to create a “faux” foreign server in your database:
CREATE SERVER my_server FOREIGN DATA WRAPPER file_fdw ;
Next, you must register the tables. You can place foreign tables in any schema you want.
We usually create a separate schema to house foreign data. For this example, we'll use
our staging schema, as shown in Example 10-1 .
Example 10-1. Make a foreign table from a delimited file
CREATE FOREIGN TABLE staging . devs ( developer VARCHAR ( 150 ), company VARCHAR ( 150 ))
SERVER my_server
OPTIONS ( format 'csv' , header 'true' , filename '/postgresql_book/ch10/devs.psv' ,
delimiter '|' , null ''
);
In our example, even though we're registering a pipe-delimited file, we still use the csv
option. A CSV file, as far as FDW is concerned, represents any file delimited by specified
characters, regardless of delimiter.
When the setup is finished, you can finally query your pipe-delimited file directly:
SELECT * FROM staging . devs WHERE developer LIKE 'T%' ;
Once you no longer need our foreign table, you can drop it:
DROP FOREIGN TABLE staging . devs ;