This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Variables

vsql provides variable substitution features similar to common Linux command shells.

1: AUTOCOMMIT
2: DBNAME
3: ECHO
4: ECHO_HIDDEN
5: ENCODING
6: HISTCONTROL
7: HISTSIZE
8: HOST
9: IGNOREEOF
10: ON_ERROR_STOP
11: PORT
12: PROMPT1 PROMPT2 PROMPT3
13: QUIET
14: ROWS_AT_A_TIME
15: SINGLELINE
16: SINGLESTEP
17: USER
18: VERBOSITY
19: VSQL_HOME
20: VSQL_SSLMODE

vsql provides variable substitution features similar to common Linux command shells. Variables are name/value pairs, where the value can be a string of any length. To set variables, use the vsql meta-command \set. For example, the following statement sets the variable fact to the value dim:

=> \set fact dim

If you call \set on a variable and supply no value, the variable is set to an empty string.

Note

The arguments of \set are subject to the same substitution rules as with other commands. For example, \set dim :fact is a valid way to copy a variable.

Getting variables

To retrieve the content of a given variable, precede the name with a colon and use it as the argument of any slash command. For example:

=> \echo :fact
dim

An unqualified \set command returns all current variables and their values:

dbadmin=> \set
VERSION = 'vsql'
AUTOCOMMIT = 'off'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'dbadmin'
USER = 'dbadmin'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
HISTSIZE = '500'

Deleting variables

To unset (or delete) a variable, use the vsql meta-command \unset.

Variable naming conventions

vsql internal variable names can contain letters, numbers, and underscores in any order and any number. Some variables are treated specially by vsql. They indicate certain option settings that can be changed at run time by altering the value of the variable or represent some state of the application. Although you can use these variables for any other purpose, this is not recommended. By convention, all specially treated variables consist of all upper-case letters (and possibly numbers and underscores). To ensure maximum compatibility in the future, avoid using such variable names for your own purposes.

SQL interpolation

You can substitute ("interpolate") vsql variables into regular SQL statements. You do so by prepending the variable name with a colon (:). For example, the following statements query the table my_table:

=> \set fact 'my_table'
=> SELECT * FROM :fact;

The value of the variable is copied literally, so it can even contain unbalanced quotes or backslash commands. Make sure that it makes sense where you put it. Variable interpolation is not performed into quoted SQL entities. One exception applies: the contents of backquoted strings (````) are passed to a system shell, and replaced with the shell's output. See Using Backquotes to Read System Variables below.

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, to set an internal variable to the full path for a file in your UNIX user directory, you can 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.

1 - AUTOCOMMIT

When AUTOCOMMIT is set 'on', each SQL command is automatically committed upon successful completion; for example:.

When AUTOCOMMIT is set 'on', each SQL command is automatically committed upon successful completion; for example:

\set AUTOCOMMIT on

To postpone COMMIT in this mode, set the value as off.

\set AUTOCOMMIT off

If AUTOCOMMIT is empty or defined as off, SQL commands are not committed unless you explicitly issue COMMIT.

Notes

AUTOCOMMIT is off by default.
AUTOCOMMIT must be in uppercase, but the values, on or off, are case insensitive.
In autocommit-off mode, you must explicitly abandon any failed transaction by entering ABORT or ROLLBACK.
If you exit the session without committing, your work is rolled back.
Validation on vsql variables is done when they are run, not when they are set.
The COPY statement, by default, commits on completion, so it does not matter which AUTOCOMMIT mode you use, unless you issue COPY NO COMMIT. Please note that DDL statements are autocommitted.
To tell if AUTOCOMMIT is on or off, issue the set command:
```
$ \set...
AUTOCOMMIT = 'off'
...
```

AUTOCOMMIT is off if a SELECT * FROM LOCKS shows locks from the statement you just ran.

$ \set AUTOCOMMIT off
$ \set
...
AUTOCOMMIT = 'off'
...
SELECT COUNT(*) FROM customer_dimension;
 count
-------
 50000
(1 row)
SELECT node_names, object_name, lock_mode, lock_scope
FROM LOCKS;
 node_names |      object_name         | lock_mode | lock_scope
------------+--------------------------+-----------+-------------
 site01     | Table:customer_dimension | S         | TRANSACTION
(1 row)

2 - DBNAME

The name of the database to which you are currently connected.

The name of the database to which you are currently connected. DBNAME is set every time you connect to a database (including program startup), but it can be unset.

3 - ECHO

If set to all, all lines entered from the keyboard or from a script are written to the standard output before they are parsed or run.

If set to all, all lines entered from the keyboard or from a script are written to the standard output before they are parsed or run.

To select this behavior on program start-up, use the switch -a. If set to queries, vsql merely prints all queries as they are sent to the server. The switch for this is -e.

4 - ECHO_HIDDEN

When this variable is set and a backslash command queries the database, the query is first shown.

When this variable is set and a backslash command queries the database, the query is first shown. This way you can study the Vertica internals and provide similar functionality in your own programs. (To select this behavior on program start-up, use the switch -E.)

If you set the variable to the value noexec, the queries are just shown but are not actually sent to the server and run.

5 - ENCODING

The current client character set encoding.

6 - HISTCONTROL

If this variable is set to ignorespace, lines that begin with a space are not entered into the history list.

If this variable is set to ignorespace, lines that begin with a space are not entered into the history list. If set to a value of ignoredups, lines matching the previous history line are not entered. A value of ignoreboth combines the two options. If unset, or if set to any other value than those previously mentioned, all lines read in interactive mode are saved on the history list.

Source: Bash.

7 - HISTSIZE

Sets the amount of space to store the command history.

Sets the amount of space to store the command history. This value roughly approximates the number of commands vsql will store in its command history buffer. This value only impacts the number of lines stored by the current vsql session. It does not affect the history stored in the .vsql_history file.

The default value is 500.

Note

vsql multiplies the HISTSIZE value by 50 bytes (an approximation of the average length of a SQL statement) to arrive at the amount of memory to set aside for the command history. Depending on the actual length of your SQL statements, vsql may be able to store more or less commands than the value you set in HISTSIZE.

Source: Bash.

8 - HOST

The database server host you are currently connected to.

The database server host you are currently connected to. This is set every time you connect to a database (including program startup), but can be unset.

9 - IGNOREEOF

If unset, sending an EOF character (usually Control+D) to an interactive session of vsql terminates the application.

If unset, sending an EOF character (usually Control+D) to an interactive session of vsql terminates the application. If set to a numeric value, that many EOF characters are ignored before the application terminates. If the variable is set but has no numeric value, the default is 10.

Source: Bash.

10 - ON_ERROR_STOP

By default, if a script command results in an error, for example, because of a malformed command or invalid data format, processing continues.

By default, if a script command results in an error, for example, because of a malformed command or invalid data format, processing continues. If you set ON_ERROR_STOP to ON in a script and an error occurs during processing, the script terminates immediately.

For example:

=> \set ON_ERROR_STOP ON

Note

If you invoke the script on Linux with vsql -f, vsql returns with error code 3 to indicate that an error occurred in the script.

11 - PORT

The database server port to which you are currently connected.

The database server port to which you are currently connected. This is set every time you connect to a database (including program start-up), but can be unset.

12 - PROMPT1 PROMPT2 PROMPT3

These specify what the prompts vsql issues look like.

These specify what the prompts vsql issues look like. See Prompting for details.

13 - QUIET

This variable is equivalent to the command line option -q.

This variable is equivalent to the command line option -q. It is probably not too useful in interactive mode.

14 - ROWS_AT_A_TIME

ROWS_AT_A_TIME is set by default to 1000, and retrieves results as blocks of rows of that size.

ROWS_AT_A_TIME is set by default to 1000, and 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.

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 with vsql meta-command\unset to guarantee perfect alignment. However, this requires re-buffering the entire result set in memory and might cause vsql to fail if the result set is too big.

15 - SINGLELINE

This variable is equivalent to the command line option -S.

This variable is equivalent to the command line option -S.

16 - SINGLESTEP

This variable is equivalent to the command line option -s.

This variable is equivalent to the command line option -s.

17 - USER

The database user you are currently connected as.

The database user you are currently connected as. This is set every time you connect to a database (including program startup), but can be unset.

18 - VERBOSITY

This variable can be set to the values default, verbose, or terse to control the verbosity of error reports.

This variable can be set to the values default, verbose, or terse to control the verbosity of error reports.

19 - VSQL_HOME

By default, the vsql program reads configuration files from the user's home directory.

By default, the vsql program reads configuration files from the user's home directory. In cases where this is not desirable, the configuration file location can be overridden by setting the VSQL_HOME environment variable in a way that does not require modifying a shared resource.

In the following example, vsql reads configuration information out of /tmp/jsmith rather than out of ~.

# Make an alternate configuration file in /tmp/jsmith
mkdir -p /tmp/jsmith
echo "\\echo Using VSQLRC in tmp/jsmith" > /tmp/jsmith/.vsqlrc
# Note that nothing is echoed when invoked normally
vsql
# Note that the .vsqlrc is read and the following is
# displayed before the vsql prompt
#
# Using VSQLRC in tmp/jsmith
VSQL_HOME=/tmp/jsmith vsql

20 - VSQL_SSLMODE

VSQL_SSLMODE specifies how (or whether) clients (like admintools) use SSL when connecting to servers.

VSQL_SSLMODE specifies how (or whether) clients (like admintools) use SSL when connecting to servers. The default value is prefer, meaning to use SSL if the server offers it. Legal values are require, prefer, allow, and disable. This variable is equivalent to the command-line -m option (or --sslmode).