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 (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
- 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