FileSystem Foreign Data Wrapper

Purpose

This fdw can be used to access data stored in various files, in a filesystem. The files are looked up based on a pattern, and parts of the file’s path are mapped to various columns, as well as the file’s content itself.

Supports:

Dependencies

No dependency outside the standard python distribution.

Options

root_dir (required)
The base directory from which the pattern is evaluated. The files in this directory should be readable by the PostgreSQL user. Ex: /var/www/.
pattern (required)
A pattern defining which files to match, and wich parts of the file path are used as columns. A column name between braces defines a mapping from a path part to a column. Ex: {artist}/{album}/{trackno} - {trackname}.ogg.
content_column
If set, defines which column will contain the actual file content.
filename_column
If set, defines which column will contain the full filename.
file_mode (default: 700)
The unix permission mask to be used when creating files.

Usage Example

Supposing you want to access files in a directory structured like this:

base_dir/
    artist1/
        album1/
            01 - title1.ogg
            02 - title2.ogg
        album2/
            01 - title1.ogg
            02 - title2.ogg
    artist2/
        album1/
            01 - title1.ogg
            02 - title2.ogg
        album2/
            01 - title1.ogg
            02 - title2.ogg

You can access those files using a foreign table like this:

CREATE SERVER filesystem_srv foreign data wrapper multicorn options (
    wrapper 'multicorn.fsfdw.FilesystemFdw'
);


CREATE FOREIGN TABLE musicfilesystem (
    artist  character varying,
    album   character varying,
    track   integer,
    title   character varying,
    content bytea,
    filename character varying
) server filesystem_srv options(
    root_dir    'base_dir',
    pattern     '{artist}/{album}/{track} - {title}.ogg',
    content_column 'content',
    filename_column 'filename')

Example:

SELECT count(track), artist, album from musicfilesystem group by artist, album;
 count | artist  | album
-------+---------+--------
     2 | artist1 | album2
     2 | artist1 | album1
     2 | artist2 | album2
     2 | artist2 | album1
(4 lines)

A filesystem foreign data wrapper.

This foreign data wrapper is based on StructuredDirectory, see https://github.com/Kozea/StructuredFS.

ReStructuredText FDW

Purpose

This fdw can be used to access metadata stored in ReStructured Text files, in a filesystem. The files are looked up based on a pattern, and parts of the file’s path are mapped to various columns, as well as the file’s content itself.

The options are exactly the same as multicorn.fsfdw itself.

If a column name is prefixed by rest_, it will not be mapped to a part of the pattern but looked up in the metadata from the ReST document.