Set the current schema

To change the current schema, use the SET SCHEMA command.

mydb.admin(admin)=> SET SCHEMA schema_one;
SET SCHEMA
mydb.schema_one(admin)=>

If you use the command inside a transaction block or within the body of a stored procedure, the schema remains in effect for the remainder of the transaction block or procedure unless you issue another SET SCHEMA command to change the current schema again.

You can use the current_tx_schema built-in function to return the value of the current transaction schema. The current_tx_schema function returns a null value if you have not used the SET SCHEMA command within the explicit transaction block or procedure body. A series of commands follows to show the behavior of schemas within an explicit transaction:

MYDB.RED(ADMIN)=> BEGIN;
BEGIN

MYDB.RED(ADMIN)=> select current_schema;
CURRENT_SCHEMA
----------------
RED
(1 row)

MYDB.RED(ADMIN)=> select current_tx_schema;
CURRENT_TX_SCHEMA
-------------------
(1 row)

MYDB.RED(ADMIN)=> set schema blue;
SET SCHEMA

MYDB.RED(ADMIN)=> select current_tx_schema;
CURRENT_TX_SCHEMA
-------------------
BLUE
(1 row)
...

As shown in the example commands, the session retains its schema of RED although the schema changes within the explicit transaction. You can use the current_tx_schema function to display the current schema for the transaction block or procedure body.