如何在Netezza nzsql中使用会话变量? [英] How do I use session variables in Netezza nzsql?

问题描述

1. 如何在Netezza nzsql中创建和使用会话变量?

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

如何将会话变量用作字符串的一部分?

How can I use session variables as part of strings?

• 我可以用字符串连接会话变量吗?
• 我可以在字符串中嵌入会话变量吗?

如何将它们用作表名或列名的一部分?

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

推荐答案

基本变量用法

文档用于Netezza nzsql中的会话变量.
它指出，为了在脚本内或在nzsql提示符下设置变量，请使用\set.

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

在会话或脚本中，您可以使用:var

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

---

高级变量用法

\set命令还具有未记录的功能，这些功能增加了更大的灵活性.
实际上，\set接受传递给它的所有值并将它们连接在一起.

---

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

此命令还支持以类似于Shell脚本的方式引用，从而使您可以在变量中包含空格.
但是请注意，带引号和不带引号的字符串仍将彼此串联在一起.

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

---

单引号

在\set命令中正确嵌入单引号可能很困难.
顺便说一句，因为双引号始终被保留，所以很少会出现问题.

---

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

在此示例中，您要使用变量:foo(其中包含bar)和文本_column选择bar_column.
以下内容将不起作用:

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

上面的示例失败，因为nzsql无法确定变量名的结尾(:foo)和其余列(_column)的名字开头.
要解决此问题，您需要通过连接:foo的值和其余列名称来使用\set创建一个新变量:

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)

如果变量包含您要使用的标识符的 end ，则无需创建任何中间变量.
在这种情况下，nzsql将正确扩展变量(例如column_:foo-> column_bar).

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).

有时您需要将变量的内容用作字符串.
考虑以下示例表和变量:

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

要解决此问题，您需要使用\set和引用知识来创建可用变量.
您可以通过组合一个单引号(正确地转义！)，该变量的内容和另一个单引号来创建一个新变量来完成此操作.

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)

这篇关于如何在Netezza nzsql中使用会话变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！