Skip to main content

Variables

MonetDB/SQL supports session variables declared by the user. They are indistinguishable from table and column names and can be used anywhere a literal constant is allowed.

declare_statement:
     DECLARE
   | DECLARE table_def

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

set_statement:
     SET ident '=' simple_atom
   | SET SESSION AUTHORIZATION ident
   | SET SCHEMA ident
   | SET user '=' ident
   | SET ROLE ident
   | SET TIME ZONE LOCAL
   | SET TIME ZONE interval_expression

user: [ USER | SESSION_USER | CURRENT_USER ]

Examples

sql>DECLARE high integer;
sql>DECLARE n varchar(256);
sql>SET high=4000;
sql>SET n='monetdb'
sql>SET trace = 'ticks,stmt'
sql>SELECT count(*) from tables where id > high;
+--------+
| count_ |
+========+
| 2      |
+--------+

The SQL variables (and environment variables) can be accessed through predefined table returning functions var() and env().The debug variable settings are defined in the MonetDB config file. The current_* variables are SQL environment settings. The trace variables is defined in the TRACE command. History denotes a boolean to log all queries.

sql>select * from var();
+------------------+
| name             |
+==================+
| debug            |
| current_schema   |
| current_user     |
| current_role     |
| optimizer        |
| trace            |
| current_timezone |
| cache            |
| history          |
+------------------+