pg_read_file()

A function for reading the contents of a text 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

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

See pg_read_binary_file().

Categories

System function

See also

pg_read_binary_file(), pg_stat_file()