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

在 MS SQL Server 中，我创建了使用可定制变量的脚本:

DECLARE @somevariable int
SELECT @somevariable = -1


INSERT INTO foo VALUES ( @somevariable )

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

如何对 PostgreSQL 客户机 psql执行相同的操作？

302307 次浏览
小开

FWIW，真正的问题是我在 set 命令的末尾加了一个分号:

设置 owner _ password‘ thepassword’;

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

Owner _ password 密码;

所以当我尝试使用它的时候:

创建角色 myrole LOGIN 未加密密码: owner _ PASSWORD NOINHERIT CREATEDBCREATEROLE 有效直到“无限”;

我有这个:

创建角色我的角色登录未加密密码密码;

这不仅未能设置文字周围的引号，而且还将命令分成了两部分(第二部分是无效的，因为它以“ NOINHERIT”开头)。

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

小开

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

  1. 如果在 SQL 语句中用单引号括起来，它们就不会展开。 因此，这种做法行不通:

    SELECT * FROM foo WHERE bar = ':myvariable'

  2. 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 second set of quotes, and the inner set has to be escaped. Thus you need:

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

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

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

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

小开
最佳答案

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;
小开

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

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

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

注意！当我在引号变量后面加上注释时，当我尝试其他答案中的一些方法时，它被吸入变量的一部分。这让我有段时间很不爽。使用这个方法注释看起来就像您期望的那样。

小开

特别是对于 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 中工作; 不能在(比如) PgAdmin-III 中工作。

这种替换发生在 psql 中的输入处理过程中，因此您不能(比方说)定义一个使用 :'filepath'的函数，并期望 :'filepath'的值在会话之间变化。当函数被定义时，它将被替换一次，然后它将成为一个常量。它对于脚本编制非常有用，但是对于运行时使用则不然。

小开

另一种方法是(ab)使用 PostgreSQLGUC 机制来创建变量。

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

我并不推荐这样做，但是它可以在一些特殊的情况下发挥作用，比如在链接问题中提到的那种情况，发布者希望通过一种方式为触发器和功能提供应用程序级别的用户名。

小开

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

我以两种方式使用它:

  • 使用 $_ 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
  • 这个表格版本也很糟糕，因为您必须偶尔清除它(而不是删除当前正在工作的会话变量)
小开

您可以尝试使用 和子句。

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;
小开

我用临时工桌解决的。

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

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

小开

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 注入的完全保护。

小开

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)

卧槽是什么? 不是 整数是 绳子！

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

我已经为这个 在另一条线上发布了一个新的解决方案。

它使用一个表来存储变量，并且可以随时更新。静态的不可变 getter 函数是动态创建的(由另一个函数创建) ，通过对表的更新触发。您可以获得很好的表存储，加上不可变的 getter 的极快速度。


提交答案