pg_table_is_visible()

A function for determining whether a table is visible in the current search path

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 ( table oid ) → 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

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);

Categories

Schema (namespace), System function