Mastering PostgreSQL 12 - Third Edition

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 the file_fdw extension. The idea is to have a module that allows you to read data from a disk and query it using SQL.

Installing the module works as expected:

CREATE EXTENSION file_fdw;

Now, we need to create a virtual server:

CREATE SERVER file_server   FOREIGN DATA WRAPPER file_fdw;

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, use the following command:

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