Variable Definitions

SQL supports variables within procedures and functions. They are indistinguishable 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

DECLARE ts1 timestamp;
SET ts1 = now(FIX);
SELECT ts1;
SELECT * FROM sys.var(FIX) WHERE name NOT IN (SELECT var_name FROM sys.var_values);</pre>

set_statement:
     SET [ schema_name . ] ident '=' simple_atom
   | SET SESSION AUTHORIZATION ident
   | SET SCHEMA ident
   | SET USER '=' ident
   | SET optimizer '=' ident
   | SET CURRENT_USER '=' ident
   | SET SESSION_USER '=' ident
   | SET ROLE ident
   | SET TIME ZONE LOCAL
   | SET TIME ZONE interval_expression

Associated system table: sys.var_values

Predefined Variables

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.

sql>select * from var();
+--------+------------------+--------------+--------------+
| schema | name             | type         | value        |
+========+==================+==============+==============+
| sys    | debug            | int          | 0            |
| sys    | current_schema   | varchar      | sys          |
| sys    | current_user     | varchar      | monetdb      |
| sys    | current_role     | varchar      | monetdb      |
| sys    | optimizer        | varchar      | default_pipe |
| sys    | current_timezone | sec_interval | 3600000      |
| sys    | last_id          | bigint       | 0            |
| sys    | rowcnt           | bigint       | 0            |
+--------+------------------+--------------+--------------+