Mastering PostgreSQL 9.6

Fetching files with file_fdw

In some cases, it can make sense to read a file from disk and expose it to PostgreSQL as a table. This is exactly what you can achieve with file_fdw extension. The idea is to have a module which allows you to read data from disk and query it using SQL.

Installing the module works as expected:

CREATE EXTENSION file_fdw; 

In the next step, we create a virtual server:

CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; 

The file_server is based on the file_fdw extension foreign data wrapper, which tells PostgreSQL how to access the file.

To expose a file as a table, the following command can be used:

CREATE FOREIGN TABLE t_passwd  (      username text,      passwd           text,      uid              int,      gid              int,      gecos            text,  dir text, ...