Contents
pg_tablespace_location()
A system function returning the file system path of the specified tablespace
pg_tablespace_location() is a system function returning the file system path of the specified tablespace.
pg_tablespace_location() was added in PostgreSQL 9.2.
Usage
pg_tablespace_location() was introduced to avoid hard-coding system paths in the pg_tablespace system catalogue table, and is used mainly by metadata queries which need to translate a tablespace's OID to its filepath, for example psql's \db slash command.
pg_tablespace_location() must be provided with the OID of the tablespace to query (note there is currently no regtablespace OID type) and returns the system file path, or NULL if the tablespace is a default one.
Change history
- PostgreSQL 9.2
- added(commit 16d8e594)
Examples
The query used by psql's \db command:
postgres=# \db
********* QUERY **********
SELECT spcname AS "Name",
pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;
**************************
List of tablespaces
Name | Owner | Location
------------+----------+----------------------------
pg_default | postgres |
pg_global | postgres |
tblspace_1 | postgres | /var/lib/pgsql/tblspc_dir1
tblspace_2 | postgres | /var/lib/pgsql/tblspc_dir2
(4 rows)
References
- PostgreSQL documentation: System Catalog Information Functions