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 ( filename text [, missing_ok boolean ] )
        → record ( size bigint, access timestamptz, modification timestamptz, change timestamptz, creation timestamptz, isdir boolean )

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

Version charts

Useful overviews

Useful links

Contents