SET / RESET VARIABLE

SereneDB supports the definition of SQL-level variables using the SET VARIABLE and RESET VARIABLE statements.

Variable Scopes

SereneDB supports two levels of variable scopes:

Scope	Description
SESSION	Variables with a SESSION scope are local to you and only affect the current session.
GLOBAL	Variables with a GLOBAL scope are specific configuration option variables that affect the entire SereneDB instance and all sessions. For example, see Set a Global Variable.

SET VARIABLE

The SET VARIABLE statement assigns a value to a variable, which can be accessed using the getvariable call:

SQL

SET VARIABLE my_var = 30;
SELECT 20 + getvariable('my_var') AS total;

 total
-------
    50

If SET VARIABLE is invoked on an existing variable, it will overwrite its value:

SQL

SET VARIABLE my_var = 30;
SET VARIABLE my_var = 100;
SELECT 20 + getvariable('my_var') AS total;

 total
-------
   120

Variables can have different types:

SQL

SET VARIABLE my_date = DATE '2018-07-13';
SET VARIABLE my_string = 'Hello world';
SET VARIABLE my_map = MAP {'k1': 10, 'k2': 20};

Variables can also be assigned to results of queries:

SQL

-- write some CSV filesCOPY (SELECT 42 AS a) TO 'test1.csv' (HEADER);
COPY (SELECT 84 AS a) TO 'test2.csv' (HEADER);
-- add a list of CSV files to a tableCREATE TABLE csv_files (file VARCHAR);
INSERT INTO csv_files VALUES ('test1.csv'), ('test2.csv');
-- initialize a variable with the list of csv filesSET VARIABLE list_of_files = (SELECT list(file) FROM csv_files);
-- read the CSV filesSELECT * FROM read_csv(getvariable('list_of_files'), filename := True);

 a  | filename
----+-----------
 42 | test1.csv
 84 | test2.csv

If a variable is not set, the getvariable function returns NULL:

SQL

SELECT getvariable('undefined_var') AS result;

 result
--------
 NULL

The getvariable function can also be used in a COLUMNS expression:

SQL

SET VARIABLE column_to_exclude = 'col1';
CREATE TABLE tbl AS SELECT 12 AS col0, 34 AS col1, 56 AS col2;
SELECT COLUMNS(c -> c != getvariable('column_to_exclude')) FROM tbl;

 col0 | col2
------+------
   12 |   56

Syntax

Syntax

RESET VARIABLE

The RESET VARIABLE statement unsets a variable.

SQL

SET VARIABLE my_var = 30;
RESET VARIABLE my_var;
SELECT getvariable('my_var') AS my_var;

 my_var
--------
 NULL

Syntax

Syntax