pg_read_file()
pg_read_file() is a system function for reading the contents of a text file on the local filesystem.
pg_read_file() was added in PostgreSQL 8.1.
Usage
pg_read_file() can be used to return the contents of any text file on the local filesystem to which the postgres system user has access.
Note that for reading structured data files into tables, the COPY command and the file_fdw foreign data wrapper will provide more convenient and efficient interfaces.
Permissions
By default pg_read_file() is restricted to superusers, but other users can be granted the EXECUTE permission to run this function.
Source
pg_read_file() is implemented in src/backend/utils/adt/genfile.c.
Change history
- PostgreSQL 9.1
- able to read an entire file, rather than specified segments (commit 03db44ea)
- PostgreSQL 8.1
- added (commit b609695b)
Examples
Read the PostgreSQL instance's postmaster.pid file:
postgres=# SELECT pg_read_file(current_setting('data_directory') || '/postmaster.pid');
pg_read_file
---------------------
3568221 +
/var/lib/pgsql/data+
1608776039 +
5432 +
/tmp +
* +
113475654 37224658+
ready +
(1 row)
Attempt to read a non-existent file:
postgres=# SELECT pg_read_file('/foo/bar.txt');
ERROR: could not open file "/foo/bar.txt" for reading: No such file or directory
Attempt to read a file for which permissions are not available:
postgres=# SELECT pg_read_file('/root/password.txt');
ERROR: could not open file "/root/password.txt" for reading: Permission denied
Attempting to read a binary file will probably result in an encoding error of some kind:
postgres=# SELECT pg_read_file(current_setting('data_directory') || '/global/pg_control');
ERROR: invalid byte sequence for encoding "UTF8": 0xb9
References
- PostgreSQL documentation: Generic File Access Functions