How do I use session variables in Netezza nzsql?

Question

1. How do I create and use session variables in Netezza nzsql?

2. How can I use session variables as part of strings?

   • Can I concatenate session variables with strings?
   • Can I embed session variables in strings?

3. How can I use them as part of table names or column names?

Answer 1

Basic Variable Usage

The documentation for session variables in Netezza nzsql is somewhat lacking.
It states that in order to set a variable within a script or at the nzsql prompt, you use \set.

\set var value

You can also specify the variable at the command line. This is useful for passing variables into scripts.

nzsql -v var=value

Within your session or script, you can access the value of that variable as :var

DB.TST(LLAMA)=> \set foo example_table

DB.TST(LLAMA)=> \d :foo
                       Table "EXAMPLE_TABLE"
   Attribute    |         Type          | Modifier | Default Value
----------------+-----------------------+----------+---------------
 EXAMPLE_COLUMN | CHARACTER VARYING(16) |          |
Distributed on hash: "EXAMPLE_COLUMN"

DB.TST(LLAMA)=> SELECT * FROM :foo;
 EXAMPLE_COLUMN
----------------
 Hello World

---

Advanced Variable Usage

The \set command also has undocumented capabilities that add greater flexibility.
In reality, \set takes all values passed to it and concatenates them together.

DB.TST(LLAMA)=> \set foo bar baz qux
DB.TST(LLAMA)=> \echo :foo
barbazqux

The command also supports quoting in a manner similar to shell scripts which allows you to include whitespace within your variables.
Be careful though, quoted and unquoted strings will still be concatenated with each other.

DB.TST(LLAMA)=> \set foo 'bar baz qux'
DB.TST(LLAMA)=> \echo :foo
bar baz qux

DB.TST(LLAMA)=> \set foo 'bar baz' qux
DB.TST(LLAMA)=> \echo :foo
bar bazqux

Double quoting also works to preserves whitespace. However, the double quotes will still be preserved in the variable.

DB.TST(LLAMA)=> \set foo "bar baz qux"
DB.TST(LLAMA)=> \echo :foo
"bar baz qux"

DB.TST(LLAMA)=> \set foo "bar baz" qux
DB.TST(LLAMA)=> \echo :foo
"bar baz"qux

Of course, the different types of quoting can be mixed:

DB.TST(LLAMA)=> \set foo "Hello World" 'Goodbye World'
DB.TST(LLAMA)=> \echo :foo
"Hello World"Goodbye World

---

Single Quotes

Properly embedding single quotes in a \set command can be difficult.
Incidentally, because double quotes are always preserved, they rarely present a problem.

Single quotes within an unquoted word will be preserved.

DB.TST(LLAMA)=> \set foo bar'baz'qux
DB.TST(LLAMA)=> \echo :foo
bar'baz'qux

Single quotes within a quoted word may result in issues.

DB.TST(LLAMA)=> \set foo 'bar'baz'qux'
DB.TST(LLAMA)=> \echo :foo
barbaz'qux'

Single quotes within a doubly quoted value are preserved.

DB.TST(LLAMA)=> \set foo "This'll work fine!"
DB.TST(LLAMA)=> \echo :foo
"This'll work fine!"

Single quotes on their own need to be quoted and escaped.

DB.TST(LLAMA)=> \set foo '
parse error at end of line

DB.TST(LLAMA)=> \set foo \'
Invalid command \'. Try \? for help.

DB.TST(LLAMA)=> \set foo '\''
DB.TST(LLAMA)=> \echo :foo
'

When in doubt: single quote the phrase and escape all remaining single quotes with backslashes.

DB.TST(LLAMA)=> \set foo '\'bar\'baz\'qux\''
DB.TST(LLAMA)=> \echo :foo
'bar'baz'qux'

---

Variables In Identifiers

Occasionally you will need to use a variable as part of an identifier (i.e. a column or table name).
Consider the following example table and variable:

DB.TST(LLAMA)=> SELECT * FROM example_table;
 BAR_COLUMN  | QUX_COLUMN
-------------+-------------
 This is bar | This is qux
(1 row)

DB.TST(LLAMA)=> \set foo bar

In this example, you want to select bar_column using your variable :foo (which contains bar) and the text _column.
The following will not work:

DB.TST(LLAMA)=> SELECT :foo_column FROM example_table;
foo_column:
ERROR:  'SELECT  FROM example_table;'
error            ^ found "FROM" (at char 9) expecting an identifier found a keyword

The above example fails because nzsql cannot determine where the variable name ends (:foo) and the remaining column (_column) name begins.
To fix this, you need to make a new variable with \set by concatenating the value of :foo and the rest of the column name:

DB.TST(LLAMA)=> \set fixed_foo :foo _column
DB.TST(LLAMA)=> \echo :fixed_foo
bar_column

DB.TST(LLAMA)=> SELECT :fixed_foo FROM example_table;
 BAR_COLUMN
-------------
 This is bar
(1 row)

If the variable contains the end of the identifier you wish to use, no intermediate variables need to be created.
In that specific case, nzsql will properly expand the variable (e.g. column_:foo -> column_bar).

---

Stringification

Sometimes you will need to use the contents of a variable as a string.
Consider the following example table and variable:

DB.TST(LLAMA)=> SELECT * FROM example_table;
 EXAMPLE_COLUMN
----------------
 Hello World
 Whatever
 Something
(3 rows)

DB.TST(LLAMA)=> \set foo Something

If you simply quote the variable within the statement then it will be treated as literal text.

DB.TST(LLAMA)=> SELECT * FROM example_table WHERE example_column = 'Something';
 EXAMPLE_COLUMN
----------------
 Something
(1 row)

DB.TST(LLAMA)=> SELECT * FROM example_table WHERE example_column = ':foo';
 EXAMPLE_COLUMN
----------------
(0 rows)

DB.TST(LLAMA)=> \p
SELECT * FROM example_table WHERE example_column = ':foo';

If you leave the variable unquoted then it will be used as an identifier.

DB.TST(LLAMA)=> SELECT * FROM example_table WHERE example_column = :foo;
ERROR:  Attribute 'SOMETHING' not found

To fix this, you need to use \set and your knowledge of quoting to create a usable variable.
You can accomplish this by making a new variable by combining a single quote (properly escaped!), the variable's contents, and another single quote.

DB.TST(LLAMA)=> \set quoted_foo '\'' :foo '\''
DB.TST(LLAMA)=> \echo :quoted_foo
'Something'

DB.TST(LLAMA)=> SELECT * FROM example_table WHERE example_column = :quoted_foo;
 EXAMPLE_COLUMN
----------------
 Something
(1 row)

If your variable needs to be used inside of a string, it may be easier to stringify your variable and use regular string concatenation.

DB.TST(LLAMA)=> SELECT * FROM example_table WHERE example_column LIKE '%ello%';
 EXAMPLE_COLUMN
----------------
 Hello World
(1 row)

DB.TST(LLAMA)=> \set foo ello
DB.TST(LLAMA)=> \set quoted_foo '\'' :foo '\''
DB.TST(LLAMA)=> \echo :quoted_foo
'ello'

DB.TST(LLAMA)=> SELECT * FROM example_table WHERE example_column LIKE '%' || :quoted_foo || '%';
 EXAMPLE_COLUMN
----------------
 Hello World
(1 row)