pg_ls_dir()

A system function for examining the contents of a directory

pg_ls_dir() is a system function for examining the contents of a directory on the local filesystem.

pg_ls_dir() was added in PostgreSQL 8.1.

Usage

pg_ls_dir ( dirname text [, missing_ok boolean, include_dot_dirs boolean ] ) → setof text

pg_ls_dir() can be used to return the contents of any directory on the local filesystem to which the postgres system user has access.

It returns the file system objects unsorted, so is usually combined with ORDER BY.

Beginning with PostgreSQL 9.5, pg_ls_dir() can optionally return the special directories "." and "..", and optionally not return an error if the specified directory is not found

Permissions

By default pg_ls_dir() is restricted to superusers, but other users can be granted the EXECUTE permission to run this function.

Note that unlike the other pg_ls_...() functions, pg_ls_dir() can be used to access directories anywhere on the filesystem, not just those used by PostgreSQL, and is therefore not available by default to members of the pg_monitor default role.

Source code

pg_ls_dir() is implemented in src/backend/utils/adt/genfile.c.

Change history

PostgreSQL 9.5
- options missing_ok and include_dot_dirs added (commit cb2acb10)
PostgreSQL 8.1
- added (commit b609695b)

Examples

Basic execution of pg_ls_dir():

postgres=# SELECT pg_ls_dir('/') ORDER BY 1 LIMIT 5;
  pg_ls_dir   
--------------
 bin
 boot
 dev
 etc
 home
(5 rows)

Include the special "dot" directories:

postgres=# SELECT pg_ls_dir(current_setting('data_directory') || '/pg_stat_tmp', FALSE, TRUE) ORDER BY 1;
       pg_ls_dir       
-----------------------
 .
 ..
 db_0.stat
 db_13832.stat
 db_16385.stat
 global.stat
 pgss_query_texts.stat
(7 rows)

Attempt to read a directory which might not exist:

postgres=# SELECT pg_ls_dir('/foo');
ERROR:  could not open directory "/foo": No such file or directory

Safely attempt to read a directory which might not exist:

postgres=# SELECT pg_ls_dir('/foo', TRUE, FALSE);
 pg_ls_dir 
-----------
(0 rows)

Note that attempting to read a directory which is not accessible to the postgres system user will always result in an error:

postgres=# SELECT pg_ls_dir('/root');
ERROR:  could not open directory "/root": Permission denied

postgres=# SELECT pg_ls_dir('/root', TRUE, FALSE);
ERROR:  could not open directory "/root": Permission denied

References

PostgreSQL documentation: Generic File Access Functions

Version charts

Useful overviews

Useful links

Contents