SET SEARCH_PATH

Specifies the order in which Vertica searches schemas when a SQL statement specifies a table name that is unqualified by a schema name. SET SEARCH_PATH overrides the current session's search path, which is initially set from the user profile. This search path remains in effect until the next SET SEARCH_PATH statement, or the session ends. For details, see Setting search paths.

To view the current search path, use SHOW SEARCH_PATH.

Syntax

SET SEARCH_PATH { TO | = } { schema-list | DEFAULT }

Parameters

schema-list

A comma-delimited list of schemas that indicates the order in which Vertica searches schemas for a table whose name is unqualified by a schema name.

If the search path includes a schema that does not exist, or for which the user lacks access privileges, Vertica silently skips over that schema.

DEFAULT

Sets the search path to the database default:

"$user", public, v_catalog, v_monitor, v_internal

Privileges

None

Examples

Show the current search path:

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

Reset the search path to schemas store and public:

=> SET SEARCH_PATH TO store, public;
=> SHOW SEARCH_PATH;
    name     |                     setting
-------------+-------------------------------------------------
 search_path | store, public, v_catalog, v_monitor, v_internal
(1 row)

Reset the search path to the database default settings:

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