Contents
pg_table_is_visible()
pg_table_is_visible() is a system function for determining whether a relation (table, view or materialized view) is visible in the current schema search path.
pg_table_is_visible() was added in PostgreSQL 7.3.
Usage
pg_table_is_visible (tableoid) →boolean
The table parameter can be provided as a text value with the regclass OID alias containing the relation's name. In this case the name should be provided as a schema-qualified value (e.g. "someschema.sometable"), otherwise an ERROR will be raised if the relation exists but is not visible in the current search path.
Change history
- PostgreSQL 7.3
- added (commit 4ab8e690)
Examples
Example usage for pg_table_is_visible(), assuming the following relations were created in the foo schema:
postgres=# CREATE TABLE foo.bar (id int); CREATE TABLE postgres=# CREATE VIEW foo.baz AS SELECT * FROM foo.bar; CREATE VIEW postgres=# CREATE MATERIALIZED VIEW foo.bop AS SELECT * FROM foo.bar; SELECT 0
As-is, none of the created relations is visible in the current search path:
postgres=# SHOW search_path; search_path ----------------- "$user", public (1 row) postgres=# SELECT pg_table_is_visible('foo.bar'::regclass); pg_table_is_visible --------------------- f (1 row) postgres=# SELECT pg_table_is_visible('foo.baz'::regclass); pg_table_is_visible --------------------- f (1 row) postgres=# SELECT pg_table_is_visible('foo.bop'::regclass); pg_table_is_visible --------------------- f (1 row)
An ERROR will be raised if the relation does not exist, or exists but is not schema-qualified and not in the current search path:
postgres=# SELECT pg_table_is_visible('foo'::regclass);
ERROR: relation "foo" does not exist
LINE 1: SELECT pg_table_is_visible('foo'::regclass);
postgres=# SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
postgres=# SELECT pg_table_is_visible('bar'::regclass);
ERROR: relation "bar" does not exist
LINE 1: SELECT pg_table_is_visible('bar'::regclass);
References
- PostgreSQL documentation: Schema Visibility Inquiry Functions