SET

Description

Modifies GUC parameters.

Precautions

Most GUC parameters can be modified at runtime by using SET, but some parameters cannot be modified after a service or session starts.

Syntax

Set the time zone.

SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT };

Set the schema.

SET [ SESSION | LOCAL ] 
    {CURRENT_SCHEMA { TO | = } { schema | DEFAULT }
    | SCHEMA 'schema'};

Set the client character set.

SET [ SESSION | LOCAL ] NAMES {'charset_name' [COLLATE 'collation_name'] | DEFAULT};

Set user-defined variables.

SET @var_name := expr [, @var_name := expr] ...
SET @var_name = expr [, @var_name = expr] ...

Set other GUC parameters.

SET [ SESSION | @@SESSION. | @@ | LOCAL | @@LOCAL.]
      {config_parameter { TO | = } { expr | DEFAULT } | FROM CURRENT }};

Set the character set. Set character_set_client and character_set_results to the specified character set, and set character_set_connection to the value of character_set_database. DEFAULT indicates that character_set_client and character_set_results are set to the default values, and character_set_connection is set to the value of character_set_database.
```
SET {CHARSET | CHARACTER SET} {DEFAULT | charset_name}
```

Parameters

SESSION
Specifies that the specified parameters take effect only for the current session. If neither SESSION nor LOCAL appears, SESSION is the default value.

If this command is executed in a transaction, the effect of the command disappears after the transaction is rolled back. Once the surrounding transaction is committed, the effect will persist until the end of the session, unless the parameters are reset by another SET statement.
LOCAL
Specifies that the specified parameters take effect only for the current transaction. After COMMIT or ROLLBACK, session-level settings will take effect again.

The effect of this command lasts only until the end of the current transaction, whether the transaction is committed or not. A special case is where SET is followed by SET LOCAL within a single transaction. In this case, SET LOCAL will take effect until the end of the transaction, but after the transaction is committed, SET will take effect.
TIME ZONE
Specifies the local time zone for the current session.

Value range: a valid local time zone. The corresponding GUC parameter is TimeZone. The default value is PRC.
CURRENT_SCHEMA
Specifies the current schema.

Value range: an existing schema name. If the schema name does not exist, the value of CURRENT_SCHEMA will be empty.
SCHEMA
Same as CURRENT_SCHEMA. The schema here is a string.

Example: set schema 'public';
NAMES {'charset_name' [COLLATE 'collation_name'] | DEFAULT};
Specifies the client character encoding name.

It is equivalent to:
```
set client_encoding = charset_name; set character_set_connection = charset_name;
set collation_connection = collation_name; set character_set_results = charset_name;
```
Value range: character sets and collations supported in M-compatible mode. Currently, charset_name must be one of the character sets specified for the database.
config_parameter
Specifies the name of the configurable GUC parameter. You can use SHOW ALL to view the available GUC parameters.

Some parameters viewed by SHOW ALL cannot be set by using SET. For example, max_datanodes.
value
Specifies the new value of config_parameter. You can specify the parameter as a string constant, identifier, number, or comma-separated list. When DEFAULT is specified, the parameter is set to its default value.
SESSION | @@SESSION. | @@| LOCAL | @@LOCAL.
The specified parameter takes effect in superuser or user mode, which can be determined by the context column in the pg_settings system view. If neither GLOBAL nor SESSION appears, SESSION is the default value. The value of config_parameter can be an expression.
1. When using @@config_parameter with operators, use spaces to separate them. For example, in set @@config_parameter1=@@config_parameter1*2;, =@@ is seen as an operator. You can write the command as set @@config_parameter1= @@config_parameter1 * 2.
2. GaussDB can deliver global parameters only through the database O&M platform to prevent security risks caused by incorrect use of global parameters. Therefore, parameter values cannot be modified using the @@global syntax.

var_name
Specifies a user-defined variable name. A variable name can contain only digits, letters, underscores (_), dots (.), and dollar signs ($). If a variable name is quoted using single quotation marks ('), double quotation marks ("), or backquotes (`), other characters can be used, but the same quotation marks cannot be used. For example, characters other than single quotation marks can be used in single quotation marks.
- User-defined variables store only numeric, character, binary, and NULL types. For Boolean and signed integers, the bit and year types are converted to int8 for storage, the unsigned integer type is converted to int8 for storage, the float4 and float8 types are converted to float8 for storage, the numeric type is converted to numeric for storage, and the binary type is converted to the longblob type. The time type and character type except year are converted to longtext for storage.
- The prepare statement does not support user-defined variables.
- In the scenario where consecutive values are assigned, only when the equal sign (=) is placed at the beginning, for example, @var_name1 := @var_name2 := … := expr and @var_name1 = @var_name2 := … := expr, the values are assigned. If the equal sign (=) is placed in the middle, for example, set @var_name1 = @var_name2 := @var_name3 = @var_name4 := expr, the equal sign (=) indicates a comparison operator.
- In non-SET statements, values cannot be assigned to user variables. You can only use these statements for query.
- If the data type cannot be converted to the data type supported by the user variable, an error is reported.
expr
Specifies an expression, which can be an expression that can be directly or indirectly converted to an integer, floating point, string, bit string, or NULL.
- To prevent sensitive information leakage, do not use functions that contain sensitive information (such as passwords) in character string expressions, such as encryption and decryption functions gs_encrypt and gs_decrypt.
- If expr is set to a subquery expression or a CASE WHEN expression and the final result is a subquery expression, the user-defined variable result is the same as the direct query result. MySQL uses intermediate calculation results. The results of user-defined variables and direct query may be different.

Examples

-- Set the schema search path.
m_db=# SET search_path TO tpcds, public;

-- Set the date style to the traditional POSTGRES style (date placed before month).
m_db=# SET datestyle TO postgres,dmy;

-- Set the user variables.
m_db=# SET @a := 1;