pg_stat_file()
A system function returning metadata about a file
pg_stat_file() is a system function returning metadata about a file or other object on the local filesystem.
pg_stat_file() was added in PostgreSQL 8.1.
Usage
pg_stat_file() returns a row containing metadata about a file or other object on the local filesystem to which the postgres system user has access.
It indicates whether a filesystem object is a directory, but does not indicate if an object is a symbolic link (symlink).
Permissions
By default pg_stat_file() is restricted to superusers, but other users can be granted the EXECUTE permission to run this function.
Source
pg_stat_file() is implemented in src/backend/utils/adt/genfile.c.
Change history
- PostgreSQL 8.1
- added (commit b609695b)
Examples
Basic execution of pg_stat_file():
postgres=# SELECT * FROM pg_stat_file(current_setting('data_directory') || '/global/pg_control');
size | access | modification | change | creation | isdir
------+------------------------+------------------------+------------------------+----------+-------
8192 | 2020-12-24 11:13:59+01 | 2020-12-24 12:04:32+01 | 2020-12-24 12:04:32+01 | | f
(1 row)
Attempt to query a non-existent file:
postgres=# SELECT * FROM pg_stat_file('/foo/bar.txt');
ERROR: could not stat file "/foo/bar.txt": No such file or directory
Attempt to query a file for which permissions are not available:
postgres=# SELECT * FROM pg_stat_file('/root/bar.txt');
ERROR: could not stat file "/root/bar.txt": Permission denied
References
- PostgreSQL documentation: Generic File Access Functions