Displaying the Current Schema

[Deleted User]

Jim Knicely authored this post.

Each user session has a search path of schemas. Vertica uses this search path to find tables and user-defined functions (UDFs) that are unqualified by their schema name.

You can use the CURRENT_SCHEMA function to display the name of the current schema (i.e., the first “valid” schema in the user’s search path).

Example:

dbadmin=> SELECT user;
 current_user
--------------
 dbadmin
(1 row)

dbadmin=> SHOW search_path;
    name     |                      setting
-------------+---------------------------------------------------
 search_path | "$user", public, v_catalog, v_monitor, v_internal
(1 row)

dbadmin=> SELECT current_schema;
current_schema
----------------
 public
(1 row)

dbadmin=> CREATE SCHEMA dbadmin;
CREATE SCHEMA

dbadmin=> SELECT current_schema;
 current_schema
----------------
 dbadmin
(1 row)

dbadmin=> CREATE TABLE my_test (c INT); -- No SCHEMA specified
CREATE TABLE

dbadmin=> SELECT table_schema FROM tables WHERE table_name = 'my_test';
 table_schema
--------------
 dbadmin
(1 row)

Have fun!