What is the purpose of the CURRENT_SCHEMA() function?

[usli06]

Hi,

Can someone explain to me what the purpose of the CURRENT_SCHEMA() function? It seems like it always returns the first schema in the search path that is listed after the "$user" schema...

For example:

Code: Select all

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

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

Then if I change the SEARCH_PATH variable, the CURRENT_SCHEMA() function will return a different value:

Code: Select all

usli06=> SET search_path = "$user", vertica_wh, public, v_catalog, v_monitor, v_internal;
SET

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

usli06=> SELECT current_schema();
 current_schema
----------------
 vertica_wh
(1 row)

Thanks!

[JimKnicely]

Hello,

The CURRENT_SCHEMA() function returns the schema that will be used if you don't explicitly indicate one. That is, if you issue the CREATE TABLE jim (jim1 int); statement in vsql then the table jim will be created in the schema that the function returns.

The schema returned by the function is not always the first one after the $user schema. If there is a schema named the same as the current user and that user has rights on that schema then the $user schema is returned by the CURRENT_SCHEMA() function...

Example:

Code: Select all

dbadmin=> create user flimflam;
CREATE USER

dbadmin=> create schema flimflam;
CREATE SCHEMA

dbadmin=> grant all on schema flimflam to flimflam;
GRANT PRIVILEGE

dbadmin=> \c vtst1 flimflam
You are now connected to database "vtst1" as user "flimflam".
vtst1=> show search_path;
    name     |                      setting
-------------+---------------------------------------------------
 search_path | "$user", public, v_catalog, v_monitor, v_internal
(1 row)

vtst1=> select current_schema();
 current_schema
----------------
 flimflam
(1 row)

vtst1=> CREATE TABLE jim (jim1 int);
CREATE TABLE

vtst1=> \d jim;
                                 List of Fields by Tables
  Schema  | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
----------+-------+--------+------+------+---------+----------+-------------+-------------
 flimflam | jim   | jim1   | int  |    8 |         | f        | f           |
(1 row)

[usli06]

Thanks, Jim. Great explanation!