SET Statement

The SET statement allows you to change session variables.

The list of available session variables can be queried from table returning function sys.var().

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   |
+--------+--------------------+--------------+--------------+

SET statement syntax:

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

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

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

Examples

-- change current schema
SET SCHEMA "profiler";
SELECT CURRENT_SCHEMA;
+----------+
| %2       |
+==========+
| profiler |
+----------+

-- change current timezone
SET CURRENT_TIMEZONE = interval '3600' second;
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;
SET TIME ZONE LOCAL;
SELECT CURRENT_TIMEZONE;

-- change optimizer pipeline
SELECT sys.optimizer;
SELECT name FROM sys.optimizers order by 1;
+-----------------+
| name            |
+=================+
| default_fast    |
| default_pipe    |
| minimal_fast    |
| minimal_pipe    |
| no_mitosis_pipe |
| recursive_pipe  |
| sequential_pipe |
+-----------------+
SET sys.optimizer = 'sequential_pipe';

-- when current schema is sys, you may omit the 'sys.' prefix
SET schema sys;

SELECT optimizer;
+--------------+
| %2           |
+==============+
| default_pipe |
+--------------+

SET optimizer = 'minimal_pipe';
SELECT optimizer;
+--------------+
| %2           |
+==============+
| minimal_pipe |
+--------------+

SET optimizer = 'ceci_nest_pas_une_pipe';
-- it fails with Error: optimizer 'ceci_nest_pas_une_pipe' unknown

select sql_optimizer;
+------------+
| %2         |
+============+
| 2147483647 |
+------------+
SET sql_optimizer = 0;

select debug;
SET debug = 10;

select last_id;
select rowcnt;

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