external_file
external_file
external_file : Access external server-side files through PostgreSQL functions
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 4285 | external_file
|
external_file
|
1.2 |
UTIL
|
PostgreSQL
|
SQL
|
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
----d--
|
No
|
No
|
No
|
Yes
|
no
|
no
|
| Relationships | |
|---|---|
| Schemas | external_file |
Fixed schema external_file; superuser required.
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PIGSTY
|
1.2 |
18
17
16
15
14
|
external_file |
- |
| RPM | PIGSTY
|
1.2 |
18
17
16
15
14
|
external_file_$v |
- |
| DEB | PIGSTY
|
1.2 |
18
17
16
15
14
|
postgresql-$v-external-file |
- |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
el8.aarch64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
el9.x86_64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
el9.aarch64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
el10.x86_64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
el10.aarch64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
d12.x86_64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
d12.aarch64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
d13.x86_64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
d13.aarch64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
u22.x86_64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
u22.aarch64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
u24.x86_64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
u24.aarch64
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
PIGSTY 1.2
|
Source
pig build pkg external_file; # build rpm/debInstall
Make sure PGDG and PIGSTY repo available:
pig repo add pgsql -u # add both repo and update cacheInstall this extension with pig:
pig install external_file; # install via package name, for the active PG version
pig install external_file -v 18; # install for PG 18
pig install external_file -v 17; # install for PG 17
pig install external_file -v 16; # install for PG 16
pig install external_file -v 15; # install for PG 15
pig install external_file -v 14; # install for PG 14Create this extension with:
CREATE EXTENSION external_file;Usage
- Source: GitHub repo, README
external_fileprovides 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.
Last updated on