Variable Definitions

SQL supports user defined variables within bodies of procedures, functions and triggers. They are distinguishable from table and column names and can be used anywhere a literal constant is allowed.

Identifier resolution follows a string inner-outward scheme. A variable name in a statement block is interpreted in the context of a table, the block, the schema.

declare_statement:
     DECLARE variable_list
   | DECLARE table_def

variable_list:
     ident [ ',' ... ] data_type
   | variable_list ',' ident [ ',' ... ] data_type

set_statement:
     SET ident '=' simple_atom

Variables can not be declared as global variables.

DECLARE ts1 timestamp;
DECLARE high integer;
DECLARE n varchar(256);

SET ts1 = now();
SET high = 4000;
SET n = 'monetdb';

SELECT ts1;

SELECT count(*) count_ from tables where id > high;
+--------+
| count_ |
+========+
| 2      |
+--------+

Declared variables are not persistent. They will be lost after closing the connection or session.

Predefined Variables

The list of available session variables can be queried from table returning 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   |
+--------+--------------------+--------------+--------------+

They can be changed in a session via Set statement.