在MS SQL Server中，我创建了脚本以使用可自定义的变量：
1
2
3
4
| DECLARE @somevariable INT
SELECT @somevariable = -1
INSERT INTO foo VALUES ( @somevariable ) |
然后，我将在运行时更改@somevariable的值，具体取决于我在特定情况下所需的值。 由于它位于脚本的顶部，因此很容易看到和记住。
我如何对PostgreSQL客户端psql做同样的事情？
Postgres变量是通过 set命令创建的，例如...
...，然后可以替换为...
1
| SELECT * FROM :myvariable.table1; |
... 要么 ...
1
| SELECT * FROM table1 WHERE :myvariable IS NULL; |
编辑：从psql 9.1开始，变量可以用引号引起来，如下所示：
1
2
3
| \SET myvariable VALUE
SELECT * FROM table1 WHERE column1 = :'myvariable'; |
在旧版本的psql客户端中：
...如果要将变量用作条件字符串查询中的值，例如...
1
| SELECT * FROM table1 WHERE column1 = ':myvariable'; |
...那么您需要在变量本身中包含引号，因为上述内容将无法正常工作。而是这样定义您的变量...
但是，如果像我一样，您遇到了要从现有变量中创建字符串的情况，我发现窍门就是...
1
| \SET quoted_myvariable '\'' :myvariable '\'' |
现在，您同时具有相同字符串的带引号和不带引号的变量！而且你可以做这样的事情....
1
| INSERT INTO :myvariable.table1 SELECT * FROM table2 WHERE column1 = :quoted_myvariable; |
关于PSQL变量的最后一句话：
如果将它们用SQL语句括在单引号中，它们将不会扩展。
因此，这不起作用：
1
| SELECT * FROM foo WHERE bar = ':myvariable' |
要在SQL语句中扩展为字符串文字，必须在变量集中包括引号。但是，变量值已经必须用引号引起来，这意味着您需要第二组引号，并且必须对内部引号进行转义。因此，您需要：
1
2
| \SET myvariable '\'somestring\''
SELECT * FROM foo WHERE bar = :myvariable |
编辑：从PostgreSQL 9.1开始，您可以改写：
1
2
| \SET myvariable somestring
SELECT * FROM foo WHERE bar = :'myvariable' |
您可以尝试使用WITH子句。
1
2
3
| 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; |
特别是对于psql，您也可以从命令行传递psql变量。您可以使用-v传递它们。这是一个用法示例：
1
2
3
4
5
6
| $ psql -v filepath=/path/TO/my/directory/mydatafile.data regress
regress=> SELECT :'filepath';
?COLUMN?
---------------------------------------
/path/TO/my/directory/mydatafile.data
(1 ROW) |
注意，冒号未加引号，然后变量名称" self"被加引号。我知道语法很奇怪。这仅在psql中有效； (例如)PgAdmin-III无法使用。
这种替换发生在psql中的输入处理期间，因此您不能(说)定义一个使用:'filepath'的函数，并且期望:'filepath'的值在会话之间改变。定义函数后，它将被替换一次，之后将是一个常量。这对于脚本编写很有用，但对运行时不起作用。
FWIW，真正的问题是我在 set命令的末尾添加了分号：
\set owner_password 'thepassword';
分号被解释为变量中的实际字符：
\echo :owner_password
thepassword;
因此，当我尝试使用它时：
CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD :owner_password NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';
...我懂了：
CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD thepassword; NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';
这不仅无法在文字周围设置引号，而且将命令分为两部分(第二部分无效，因为它以" NOINHERIT"开头)。
这个故事的寓意：PostgreSQL"变量"实际上是文本扩展中使用的宏，而不是真正的值。我敢肯定这会派上用场，但一开始它很棘手。
您需要使用一种过程语言，例如PL / pgSQL而不是SQL proc语言。
在PL / pgSQL中，可以在SQL语句中直接使用vars。
对于单引号，可以使用引号文字函数。
postgres(从9.0版开始)允许使用任何受支持的服务器端脚本语言的匿名块
1
2
3
4
5
6
| DO '
DECLARE somevariable int = -1;
BEGIN
INSERT INTO foo VALUES ( somevariable );
END
' ; |
http://www.postgresql.org/docs/current/static/sql-do.html
由于所有内容都在字符串内部，因此需要对转义的外部字符串变量进行转义和引用两次。取而代之的是，使用美元引号不能完全防止SQL注入。
另一种方法是(ab)使用PostgreSQL GUC机制创建变量。有关详细信息和示例，请参见此先前的答案。
您在postgresql.conf中声明GUC，然后在运行时使用SET命令更改其值，并通过current_setting(...)获取其值。
我不建议将此方法用于一般用途，但在链接问题中提到的狭窄情况下可能很有用，在这种情况下，发帖者需要一种为触发器和函数提供应用程序级用户名的方法。
我用一个临时表解决了它。
1
2
3
4
| CREATE TEMP TABLE temp_session_variables (
"sessionSalt" TEXT
);
INSERT INTO temp_session_variables ("sessionSalt") VALUES (CURRENT_TIMESTAMP || RANDOM()::TEXT); |
这样，我有一个"变量"可以在多个查询中使用，这对于会话是唯一的。我需要它来生成唯一的"用户名"，而如果导入具有相同用户名的用户时仍然没有冲突。
我真的很想念那个功能。实现类似目的的唯一方法是使用函数。
我以两种方式使用它：
-
使用$ _SHARED变量的perl函数
-
将变量存储在表中
Perl版本：
1
2
3
4
5
6
| 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; |
表格版本：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| 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结合使用
-
该表版本也很糟糕，因为您必须偶尔清除它(而不是删除当前正在工作的会话变量)
我发现这个问题和答案非常有用，但也令人困惑。我很难让带引号的变量起作用，所以这是使它起作用的方式：
1
2
3
| \SET deployment_user username -- username
\SET deployment_pass '\'string_password\''
ALTER USER :deployment_user WITH PASSWORD :deployment_pass; |
这样，您可以在一个语句中定义变量。使用它时，单引号将嵌入到变量中。
注意！当我在带引号的变量后添加注释时，当我尝试其他答案中的某些方法时，它作为变量的一部分被吸收。那真是让我有些困惑。使用这种方法，注释似乎可以按照您的期望进行处理。
我在另一个线程上发布了一个新的解决方案。
它使用表来存储变量，并且可以随时更新。通过更新表触发动态创建静态不可变getter函数(由另一个函数)。您将获得漂亮的表存储空间，以及不可变的吸气剂的超快速度。
psql中的变量很烂。如果要声明整数，则必须输入整数，然后执行回车，然后以分号结束语句。观察：
假设我要声明一个整数变量my_var并将其插入表test中：
示例表test：
1
2
3
4
5
6
7
| 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) |
显然，此表中没有任何内容：
1
2
3
4
| thedatabase=# SELECT * FROM test;
id
----
(0 ROWS) |
我们声明一个变量。注意下一行的分号是如何！
1
2
| thedatabase=# \SET my_var 999
thedatabase=# ; |
现在我们可以插入。我们必须使用这种奇怪的" :''"语法：
1
2
| thedatabase=# INSERT INTO test(id) VALUES (:'my_var');
INSERT 0 1 |
有效！
1
2
3
4
5
| thedatabase=# SELECT * FROM test;
id
-----
999
(1 ROW) |
说明：
那么...如果下一行没有分号怎么办？变量？看一看：
我们声明my_var而不换行。
1
| thedatabase=# \SET my_var 999; |
让我们选择my_var。
1
2
3
4
5
| thedatabase=# SELECT :'my_var';
?COLUMN?
----------
999;
(1 ROW) |
WTF是吗？它不是整数，而是字符串999;！
1
2
3
4
5
| thedatabase=# SELECT 999;
?COLUMN?
----------
999
(1 ROW) |
