SQL 你如何在psql中使用脚本变量？

Question

提问by Craig Walker

In MS SQL Server, I create my scripts to use customizable variables:

在 MS SQL Server 中，我创建脚本以使用可自定义的变量：

DECLARE @somevariable int  
SELECT @somevariable = -1

INSERT INTO foo VALUES ( @somevariable )

I'll then change the value of @somevariableat runtime, depending on the value that I want in the particular situation. Since it's at the top of the script it's easy to see and remember.

然后，我将@somevariable在运行时更改的值，具体取决于我在特定情况下所需的值。因为它位于脚本的顶部，所以很容易看到和记住。

How do I do the same with the PostgreSQL client psql?

我如何对 PostgreSQL 客户端做同样的事情psql？

Answer 1

回答by crowmagnumb

Postgres variables are created through the \set command, for example ...

Postgres 变量是通过 \set 命令创建的，例如...

\set myvariable value

... and can then be substituted, for example, as ...

... 然后可以替换，例如，作为 ...

SELECT * FROM :myvariable.table1;

... or ...

... 或者 ...

SELECT * FROM table1 WHERE :myvariable IS NULL;

edit: As of psql 9.1, variables can be expanded in quotes as in:

编辑：从 psql 9.1 开始，变量可以在引号中展开，如下所示：

\set myvariable value 

SELECT * FROM table1 WHERE column1 = :'myvariable';

In older versions of the psql client:

在旧版本的 psql 客户端中：

... If you want to use the variable as the value in a conditional string query, such as ...

... 如果要在条件字符串查询中使用该变量作为值，例如 ...

SELECT * FROM table1 WHERE column1 = ':myvariable';

... then you need to include the quotes in the variable itself as the above will not work. Instead define your variable as such ...

...然后您需要在变量本身中包含引号，因为上述方法不起作用。而是这样定义你的变量......

\set myvariable 'value'

However, if, like me, you ran into a situation in which you wanted to make a string from an existing variable, I found the trick to be this ...

但是，如果像我一样，您遇到了想要从现有变量创建字符串的情况，我发现诀窍是这样的......

\set quoted_myvariable '\'' :myvariable '\''

Now you have both a quoted and unquoted variable of the same string! And you can do something like this ....

现在您拥有同一个字符串的带引号和不带引号的变量！你可以做这样的事情......

INSERT INTO :myvariable.table1 SELECT * FROM table2 WHERE column1 = :quoted_myvariable;

Answer 2

回答by Craig Walker

One final word on PSQL variables:

关于 PSQL 变量的最后一句话：

They don't expand if you enclose them in single quotes in the SQL statement. Thus this doesn't work:
```
SELECT * FROM foo WHERE bar = ':myvariable'
```
To expand to a string literal in a SQL statement, you have to include the quotes in the variable set. However, the variable value already has to be enclosed in quotes, which means that you need a secondset of quotes, and the inner set has to be escaped. Thus you need:
```
\set myvariable '\'somestring\''  
SELECT * FROM foo WHERE bar = :myvariable
```
EDIT: starting with PostgreSQL 9.1, you may write instead:
```
\set myvariable somestring
SELECT * FROM foo WHERE bar = :'myvariable'
```

如果在 SQL 语句中将它们用单引号括起来，它们就不会展开。因此这不起作用：
```
SELECT * FROM foo WHERE bar = ':myvariable'
```
要在 SQL 语句中扩展为字符串文字，您必须在变量集中包含引号。但是，变量值已经必须用引号括起来，这意味着您需要第二组引号，并且必须对内部组进行转义。因此你需要：
```
\set myvariable '\'somestring\''  
SELECT * FROM foo WHERE bar = :myvariable
```
编辑：从 PostgreSQL 9.1 开始，您可以改为编写：
```
\set myvariable somestring
SELECT * FROM foo WHERE bar = :'myvariable'
```

Answer 3

回答by skaurus

You can try to use a WITHclause.

您可以尝试使用WITH子句。

WITH vars AS (SELECT 42 AS answer, 3.14 AS appr_pi)
SELECT t.*, vars.answer, t.radius*vars.appr_pi
FROM table AS t, vars;

Answer 4

回答by Craig Ringer

Specifically for psql, you can pass psqlvariables from the command line too; you can pass them with -v. Here's a usage example:

特别是对于psql，您也可以psql从命令行传递变量；你可以用-v. 这是一个使用示例：

$ psql -v filepath=/path/to/my/directory/mydatafile.data regress
regress=> SELECT :'filepath';
               ?column?                
---------------------------------------
 /path/to/my/directory/mydatafile.data
(1 row)

Note that the colon is unquoted, then the variable name its self is quoted. Odd syntax, I know. This only works in psql; it won't work in (say) PgAdmin-III.

请注意，冒号是不带引号的，然后变量名它的自身被引用。奇怪的语法，我知道。这只适用于 psql；它在（比如）PgAdmin-III 中不起作用。

This substitution happens during input processing in psql, so you can't (say) define a function that uses :'filepath'and expect the value of :'filepath'to change from session to session. It'll be substituted once, when the function is defined, and then will be a constant after that. It's useful for scripting but not runtime use.

这种替换发生在 psql 的输入处理期间，因此您不能（例如）定义一个函数，该函数使用:'filepath'并期望的值在:'filepath'会话之间发生变化。当函数被定义时，它将被替换一次，然后将是一个常量。它对编写脚本很有用，但不适用于运行时使用。

Answer 5

回答by Craig Walker

FWIW, the real problem was that I had included a semicolon at the end of my \set command:

FWIW，真正的问题是我在 \set 命令的末尾包含了一个分号：

\set owner_password 'thepassword';

The semicolon was interpreted as an actual character in the variable:

分号被解释为变量中的实际字符：

\echo :owner_password thepassword;

So when I tried to use it:

所以当我尝试使用它时：

CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD :owner_password NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

...I got this:

...我懂了：

CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD thepassword; NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD thepassword; NOINHERIT CREATEDB CREATEROLE 有效期至“无穷大”；

That not only failed to set the quotes around the literal, but split the command into 2 parts (the second of which was invalid as it started with "NOINHERIT").

这不仅没有在文字周围设置引号，而且将命令分成两部分（第二部分无效，因为它以“NOINHERIT”开头）。

The moral of this story: PostgreSQL "variables" are really macros used in text expansion, not true values. I'm sure that comes in handy, but it's tricky at first.

这个故事的寓意是：PostgreSQL“变量”实际上是用于文本扩展的宏，而不是真正的值。我相信这会派上用场，但一开始很棘手。

Answer 6

回答by Craig Walker

You need to use one of the procedural languages such as PL/pgSQL not the SQL proc language. In PL/pgSQL you can use vars right in SQL statements. For single quotes you can use the quote literal function.

您需要使用一种过程语言，例如 PL/pgSQL 而不是 SQL proc 语言。在 PL/pgSQL 中，您可以在 SQL 语句中直接使用变量。对于单引号，您可以使用引用文字函数。

Answer 7

回答by Jasen

postgres (since version 9.0) allows anonymous blocks in any of the supported server-side scripting languages

postgres（从 9.0 版开始）允许任何支持的服务器端脚本语言中的匿名块

DO '
DECLARE somevariable int = -1;
BEGIN
INSERT INTO foo VALUES ( somevariable );
END
' ;

http://www.postgresql.org/docs/current/static/sql-do.html

As everything is inside a string, external string variables being substituted in will need to be escaped and quoted twice. Using dollar quoting instead will not give full protection against SQL injection.

由于所有内容都在字符串中，因此需要对被替换的外部字符串变量进行转义和引用两次。改用美元引用并不能完全防止 SQL 注入。

Answer 8

回答by Craig Ringer

Another approach is to (ab)use the PostgreSQL GUC mechanism to create variables. See this prior answerfor details and examples.

另一种方法是（ab）使用 PostgreSQL GUC 机制来创建变量。有关详细信息和示例，请参阅此先前的答案。

You declare the GUC in postgresql.conf, then change its value at runtime with SETcommands and get its value with current_setting(...).

您在中声明 GUC postgresql.conf，然后在运行时使用SET命令更改其值并使用获取其值current_setting(...)。

I don't recommend this for general use, but it could be useful in narrow cases like the one mentioned in the linked question, where the poster wanted a way to provide the application-level username to triggers and functions.

我不建议将其用于一般用途，但在链接问题中提到的狭窄情况下可能很有用，在这种情况下，发布者想要一种为触发器和函数提供应用程序级用户名的方法。

Answer 9

回答by geon

I solved it with a temp table.

我用临时表解决了它。

CREATE TEMP TABLE temp_session_variables (
    "sessionSalt" TEXT
);
INSERT INTO temp_session_variables ("sessionSalt") VALUES (current_timestamp || RANDOM()::TEXT);

This way, I had a "variable" I could use over multiple queries, that is unique for the session. I needed it to generate unique "usernames" while still not having collisions if importing users with the same user name.

这样，我就有了一个可以在多个查询中使用的“变量”，这对于会话来说是唯一的。如果导入具有相同用户名的用户，我需要它来生成唯一的“用户名”，同时仍然不会发生冲突。

Answer 10

回答by Kaiko Kaur

I really miss that feature. Only way to achieve something similar is to use functions.

我真的很想念那个功能。实现类似目标的唯一方法是使用函数。

I have used it in two ways:

我以两种方式使用它：

perl functions that use $_SHARED variable
store your variables in table

使用 $_SHARED 变量的 perl 函数
将变量存储在表中

Perl version:

Perl 版本：

   CREATE FUNCTION var(name text, val text) RETURNS void AS $$
        $_SHARED{$_[0]} = $_[1];
   $$ LANGUAGE plperl;
   CREATE FUNCTION var(name text) RETURNS text AS $$
        return $_SHARED{$_[0]};
   $$ LANGUAGE plperl;

Table version:

表版：

CREATE TABLE var (
  sess bigint NOT NULL,
  key varchar NOT NULL,
  val varchar,
  CONSTRAINT var_pkey PRIMARY KEY (sess, key)
);
CREATE FUNCTION var(key varchar, val anyelement) RETURNS void AS $$
  DELETE FROM var WHERE sess = pg_backend_pid() AND key = ;
  INSERT INTO var (sess, key, val) VALUES (sessid(), , ::varchar);
$$ LANGUAGE 'sql';

CREATE FUNCTION var(varname varchar) RETURNS varchar AS $$
  SELECT val FROM var WHERE sess = pg_backend_pid() AND key = ;
$$ LANGUAGE 'sql';

Notes:

笔记：

plperlu is faster than perl
pg_backend_pid is not best session identification, consider using pid combined with backend_start from pg_stat_activity
this table version is also bad because you have to clear this is up occasionally (and not delete currently working session variables)

plperlu 比 perl 快
pg_backend_pid 不是最好的会话识别，考虑使用 pid 结合 pg_stat_activity 中的 backend_start
这个表版本也很糟糕，因为你必须偶尔清除它（而不是删除当前工作的会话变量）

SQL 你如何在psql中使用脚本变量？

提问by Craig Walker

回答by crowmagnumb

回答by Craig Walker

回答by skaurus

回答by Craig Ringer

回答by Craig Walker

回答by Craig Walker

回答by Jasen

回答by Craig Ringer

回答by geon

回答by Kaiko Kaur

相关推荐

最近更新

标签

SQL 你如何在psql中使用脚本变量？

提问by Craig Walker

回答by crowmagnumb

回答by Craig Walker

回答by skaurus

回答by Craig Ringer

回答by Craig Walker

回答by Craig Walker

回答by Jasen

回答by Craig Ringer

回答by geon

回答by Kaiko Kaur

相关推荐

SQL SERVER 中的 ORDER BY AND CASE

SQL Server - 脏读的优点和缺点

SQL Excel 中是否有类似合并的函数？

推荐用于标记或标记的 SQL 数据库设计

相关推荐

最近更新

标签