Set Statement

SET statements which can be used in a session:

set-statement:
    SET SCHEMA schema_name
  | SET TIME ZONE [ '=' ] interval_expression
  | SET TIME ZONE [ '=' ] LOCAL
  | SET CURRENT_TIMEZONE [ '=' ] LOCAL
  | SET CURRENT_TIMEZONE [ '=' ] interval_expression
  | SET USER [ '=' ] user_name
  | SET CURRENT_USER [ '=' ] user_name
  | SET SESSION_USER [ '=' ] user_name
  | SET SESSION AUTHORIZATION [ '=' ] user_name
  | SET ROLE role_name
  | SET CURRENT_ROLE [ '=' ] role_name
  | SET [ sys . ] optimizer = optimizer_pipeline_name

interval_expression:
    INTERVAL 'hh:mi' HOUR TO MINUTE
  | INTERVAL 'hh' HOUR
  | INTERVAL 'mi' MINUTE
  | INTERVAL 'sec' SECOND
  | nr_of_seconds

For setting user defined variables in bodies of functions/procedures/triggers see: Variable Definitions.

Examples

-- change current schema
SET SCHEMA profiler;
SELECT CURRENT_SCHEMA;

-- change current timezone
SET CURRENT_TIMEZONE = 3600;
SET TIME ZONE LOCAL;
SET TIME ZONE INTERVAL '+02:00' HOUR TO MINUTE;
SET TIME ZONE INTERVAL '3' HOUR;
SET TIME ZONE INTERVAL '240' MINUTE;
SET TIME ZONE INTERVAL '-3600' SECOND;
SELECT CURRENT_TIMEZONE;

-- change optimizer pipeline
SELECT sys.optimizer;
SELECT * FROM sys.optimizers;
SET sys.optimizer = 'sequential_pipe';

-- when current schema is sys, you may omit the 'sys.' prefix
SET schema sys;
SELECT optimizer;
SET optimizer = 'default_fast';
SELECT optimizer;
SET optimizer = 'ceci_nest_pas_une_pipe';
-- it fails with Error: optimizer 'ceci_nest_pas_une_pipe' unknown
SELECT optimizer;

For examples on setting current user or role see: User/Role Privileges.

Session variables can also be queried via system function sys.var().

sql>select * from sys.var() order by name;
+--------+--------------------+--------------+--------------+
| schema | name               | type         | value        |
+========+====================+==============+==============+
| sys    | current_role       | varchar      | monetdb      |
| sys    | current_schema     | varchar      | sys          |
| sys    | current_timezone   | sec_interval | 7200000      |
| sys    | current_user       | varchar      | monetdb      |
| sys    | debug              | int          | 0            |
| sys    | division_min_scale | int          | 3            |
| sys    | last_id            | bigint       | 0            |
| sys    | optimizer          | varchar      | default_pipe |
| sys    | rowcnt             | bigint       | 10           |
| sys    | sql_optimizer      | int          | 2147483647   |
+--------+--------------------+--------------+--------------+