external_file

Overview

Fixed schema external_file; superuser required.

Version

Build

You can build the RPM / DEB packages for external_file using pig build:

pig build pkg external_file         # build RPM / DEB packages

Install

You can install external_file directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install external_file;          # Install for current active PG version

pig ext install -y external_file -v 18  # PG 18
pig ext install -y external_file -v 17  # PG 17
pig ext install -y external_file -v 16  # PG 16
pig ext install -y external_file -v 15  # PG 15
pig ext install -y external_file -v 14  # PG 14

dnf install -y external_file_18       # PG 18
dnf install -y external_file_17       # PG 17
dnf install -y external_file_16       # PG 16
dnf install -y external_file_15       # PG 15
dnf install -y external_file_14       # PG 14

apt install -y postgresql-18-external-file   # PG 18
apt install -y postgresql-17-external-file   # PG 17
apt install -y postgresql-16-external-file   # PG 16
apt install -y postgresql-15-external-file   # PG 15
apt install -y postgresql-14-external-file   # PG 14

Create Extension:

CREATE EXTENSION external_file;

Usage

• Source: GitHub repo, README
• external_file provides server-side access to external files through a PostgreSQL extension, similar to Oracle BFILE-style locators.

CREATE EXTENSION external_file;

The README states that objects are created in the external_file schema by default and that creating the extension requires a PostgreSQL superuser.

Core Workflow

The extension uses a directory alias plus a file name to identify an external file. The upstream README shows this sequence:

INSERT INTO directories(directory_name, directory_path)
VALUES ('temporary', '/tmp/');

INSERT INTO directory_roles(directory_name, directory_role, directory_read, directory_write)
VALUES ('temporary', 'a_role', true, false);

SELECT writeEfile('\x48656c6c6f2c0a0a596f75206172652072656164696e67206120746578742066696c652e0a0a526567617264732c0a',
                  ('temporary', 'blahblah.txt'));
SELECT readefile(the_file) FROM efile_test;
SELECT copyefile(('temporary', 'blahblah.txt'), ('temporary', 'copy_blahblah.txt'));

The main exported helpers are efilename, readEfile, writeEfile, copyEfile, and getEfilePath.

Notes

The extension does not read files directly from the server filesystem. It uses the server-side lo_* family and enforces access through directory and role tables.