pg_read_binary_file()
pg_read_binary_file() is a system function for reading the contents of a binary file on the local filesystem.
pg_read_binary_file() was added in PostgreSQL 9.1.
Usage
pg_read_binary_file() can be used to return the contents of any binary file on the local filesystem to which the postgres system user has access. The contents are returned as bytea.
Note that if reading binary files for storage in the database, the large object API may be more convenient and efficient.
Permissions
By default pg_read_file() is restricted to superusers, but other users can be granted the EXECUTE permission to run this function.
Source code
pg_read_binary_file() is implemented in src/backend/utils/adt/genfile.c.
Change history
- PostgreSQL 9.1
- added (commit 03db44ea)
Examples
Basic execution of pg_read_binary_file(), here reading the PG_VERSION file from the instance's data directory as bytea:
postgres=# SELECT pg_read_binary_file(current_setting('data_directory') || '/PG_VERSION');
pg_read_binary_file
---------------------
\x31340a
(1 row)
Convert a text file into the database's encoding:
postgres=# SELECT pg_read_file('/tmp/convert.txt'); ERROR: invalid byte sequence for encoding "UTF8": 0xfc $ cat /tmp/convert.txt �berbewertete �thiopische �dnis
$ file -i /tmp/convert.txt /tmp/convert.txt: text/plain; charset=iso-8859-1 postgres=# SELECT convert_from(pg_read_binary_file('/tmp/convert.txt'), 'ISO_8859_1'); convert_from --------------------------------- überbewertete äthiopische Ödnis+ (1 row)
Attempt to read a non-existent file:
postgres=# SELECT pg_read_binary_file('/foo/bar.bin');
ERROR: could not open file "/foo/bar.bin" for reading: No such file or directory
Attempt to read a file for which permissions are not available:
postgres=# SELECT pg_read_binary_file('/root/bar.bin');
ERROR: could not open file "/root/bar.bin" for reading: Permission denied
References
- PostgreSQL documentation: Generic File Access Functions