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

Question

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

DECLARE @somevariable int  
SELECT @somevariable = -1

INSERT INTO foo VALUES ( @somevariable )

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

如何使用 PostgreSQL 客户端 psql 执行相同操作？

Answer 1

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

由于所有内容都在字符串内，因此替换的外部字符串变量将需要转义并引用两次。 使用美元引用并不能提供针对 SQL 注入的全面保护。

Answer 2

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

Answer 3

我用临时表解决了这个问题。

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

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

Answer 4

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

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

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

Answer 5

我真的很怀念这个功能。 实现类似功能的唯一方法是使用函数。

我以两种方式使用它：

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

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;

表版本：

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 = $1;
  INSERT INTO var (sess, key, val) VALUES (sessid(), $1, $2::varchar);
$ LANGUAGE 'sql';

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

注意：

• plperlu 比 perl 更快
• pg_backend_pid 不是最好的会话标识，请考虑使用 pid 与 pg_stat_activity
• 这个表 中的 backend_start 结合使用版本也很糟糕，因为您必须偶尔清除此版本（并且不要删除当前正在工作的会话变量）

Answer 6

psql 中的变量很糟糕。 如果要声明一个整数，则必须输入该整数，然后执行回车，然后以分号结束语句。 观察：

假设我想声明一个整型变量 my_var 并将其插入到表 test 中：

示例表 test：

thedatabase=# \d test;
                         Table "public.test"
 Column |  Type   |                     Modifiers                     
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('test_id_seq'::regclass)
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

显然，其中没有任何内容表还：

thedatabase=# select * from test;
 id 
----
(0 rows)

我们声明一个变量。 注意下一行的分号！

thedatabase=# \set my_var 999
thedatabase=# ;

现在我们可以插入了。 我们必须使用这种奇怪的“:''”语法：

thedatabase=# insert into test(id) values (:'my_var');
INSERT 0 1

它有效！

thedatabase=# select * from test;
 id  
-----
 999
(1 row)

解释：

那么...如果下一行没有分号会发生什么？ 变量？ 看一下：

我们声明了没有新行的 my_var 。

thedatabase=# \set my_var 999;

让我们选择 my_var。

thedatabase=# select :'my_var';
 ?column? 
----------
 999;
(1 row)

那是什么？ 它不是一个整数，而是一个字符串 999;！

thedatabase=# select 999;
 ?column? 
----------
      999
(1 row)

Answer 7

我为此发布了一个新的解决方案 在另一个线程上。

它使用表来存储变量，并且可以随时更新。 静态不可变 getter 函数是动态创建的（由另一个函数），由表更新触发。 您将获得良好的表存储，以及不可变 getter 的极快速度。

Answer 8

我发现这个问题和答案非常有用，但也令人困惑。 我在让带引号的变量工作时遇到了很多麻烦，所以这是我让它工作的方法：

\set deployment_user username    -- username
\set deployment_pass '\'string_password\''
ALTER USER :deployment_user WITH PASSWORD :deployment_pass;

这样您就可以在一个语句中定义变量。 当您使用它时，单引号将嵌入到变量中。

笔记！ 当我在引用的变量后面添加注释时，当我尝试其他答案中的某些方法时，它会作为变量的一部分被吸收。 这确实让我搞砸了一段时间。 使用此方法，注释似乎会按照您的预期进行处理。

Answer 9

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

\setowner_password 'thepassword';

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

\echo :所有者密码
密码；

所以当我尝试使用它时：

创建角色 myrole 登录未加密密码：owner_password NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

...我懂了：

创建角色 myrole 登录未加密密码 thepassword; NOINHERIT CREATEDB CREATEROLE 直到“无穷大”为止有效；

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

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

Answer 10

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

1. 如果您在 SQL 语句中将它们用单引号引起来，它们就不会扩展。
   因此这不起作用：

   SELECT * FROM foo WHERE bar = ':myvariable' 
     

2. 要在 SQL 语句中扩展为字符串文字，您必须在变量集中包含引号。 但是，变量值已经必须用引号引起来，这意味着您需要第二组引号，并且内部组必须被转义。 因此你需要：

   \set myvariable '\'somestring\''   
     SELECT * FROM foo WHERE bar = :myvariable 
     

   编辑：从 PostgreSQL 9.1 开始，您可以改为编写：

   \set myvariable somestring 
     SELECT * FROM foo WHERE bar = :'myvariable' 
     

Answer 11

您可以尝试使用 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 12

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

\set myvariable value

... 然后可以被替换，例如 ...

SELECT * FROM :myvariable.table1;

... 或 ...

SELECT * FROM table1 WHERE :myvariable IS NULL;

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

\set myvariable value 

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

在旧版本的 psql 客户端中：

... 如果您想使用变量作为条件字符串查询中的值，例如 ...

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

...那么您需要在变量本身中包含引号，因为上面的方法不起作用。 相反，这样定义您的变量...

\set myvariable 'value'

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

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

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

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

Answer 13

特别是对于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)

请注意，冒号不带引号，然后变量名称本身被引号引起来。 我知道语法很奇怪。 这仅适用于 psql，但不适用于 -c / --command； 您必须通过 stdin 或通过 -f 发送命令。 它在（例如）PgAdmin-III 中也不起作用。

这种替换发生在 psql 的输入处理过程中，因此您不能（比如说）定义使用 :'filepath' 的函数并期望 :'filepath' 的值从一个会话到另一个会话的更改。 当函数被定义时，它将被替换一次，然后将是一个常量。 它对于脚本编写很有用，但对于运行时使用却没有用。