\set [ NAME [ VALUE [ ... ] ] ]
\set [ name [ value [ ... ] ] ]
sets the internal variable name to value or, if more than one value is given, to the concatenation of all of values. If no second argument is given, the variable is set with no value.
If no argument is provided, \set lists all internal variables; for example:
=> \set
VERSION = 'Vertica Analytic Database v6.0.0-0'
AUTOCOMMIT = 'off'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'VMartDB'
USER = 'dbadmin'
HOST = '<host_ip_address>'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
HISTSIZE = '500'
Notes
- Valid variable names are case sensitive and can contain characters, digits, and underscores. vsql treats several variables as special, which are described in Variables.
- The
\set
parameterROWS_AT_A_TIME
defaults to 1000. It retrieves results as blocks of rows of that size. The column formatting for the first block is used for all blocks, so in later blocks some entries could overflow. See \timing for examples. - When formatting results, Vertica buffers ROWS_AT_A_TIME rows in memory to calculate the maximum column widths. It is possible that rows after this initial fetch are not properly aligned if any of the field values are longer than those see in the first ROWS_AT_A_TIME rows. ROWS_AT_A_TIME can be \unset to guarantee perfect alignment, but this requires re-buffering the entire result set in memory and may cause vsql to fail if the result set is too big.
- To unset a variable, use the \unset command.
Using Backquotes to Read System Variables
In vsql, the contents of backquotes are passed to the system shell to be interpreted (the same behavior as many UNIX shells). This is particularly useful in setting internal vsql variables, since you may want to access UNIX system variables (such as HOME or TMPDIR) rather than hard-code values.
For example, if you want to set an internal variable to the full path for a file in your UNIX user directory, you could use backquotes to get the content of the system HOME variable, which is the full path to your user directory:
=> \set inputfile `echo $HOME`/myinput.txt=> \echo :inputfile /home/dbadmin/myinput.txt
The contents of the backquotes are replaced with the results of running the contents in a system shell interpreter. In this case, the echo $HOME
command returns the contents of the HOME system variable.