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

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

DECLARE @somevariable int  
SELECT @somevariable = -1

INSERT INTO foo VALUES ( @somevariable )

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

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

1> crowmagnumb..：

---

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`仅适用于`psql`工具,你不能在存储过程中使用它!

---

这个答案以一种令人困惑的方式将`psql`元命令`\ set`与PostgreSQL命令混合在一起.

---

从postgresql 9.1开始,在psql中,您现在可以使用:'variable'将其正确引用为您的值,或者:"variable"将其用作标识符.

---

`\ set myvariable'value'`确实**不包括变量中的任何引号,与此答案所说的相反.如有疑问,请在psql中使用`\ echo:myvariable`来独立显示任何查询的值.

---

@SorinSbarnea OP询问*脚本*,而不是*程序*

---

@laurent:或者像@HitScan一样使用`:'myvar'

2> Craig Walker..：

---

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

如果将它们用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'

---

为':'myvariable'`干杯

3> skaurus..：

---

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

---

它似乎不适用于`WHERE`子句.公平警告.

---

与布莱斯的报告相反,它似乎对我来说很好.`CREATE TABLE test(名称VARCHAR,年龄INT);``INSERT INTO test(名称,年龄)VALUES('Jack',21),('Jill',20);``WITH vars AS(SELECT N'Jack' AS名称,21 AS年龄)SELECT测试.*FROM测试,vars WHERE test.name = vars.name和test.age = vars.age;`输出杰克和杰克的年龄,如预期的那样.

---

对于很多用途,特别是在像Python Flask这样的Web应用程序框架的上下文中,这是在单个查询中重用复杂计算值的最佳解决方案.

4> Craig Ringer..：

---

特别适用于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'会话之间改变的函数.当定义函数时,它将被替换一次,然后在此之后将是一个常量.它对脚本有用,但不适用于运行时使用.

5> Craig Walker..：

---

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

\ set owner_password'thepassword';

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

\ echo:owner_password thepassword;

所以当我尝试使用它时:

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

...我懂了:

创造角色myrole登录UNENCRYPTED PASSWORD thepassword; NOINHERIT CREATEDB CREATEROLE有效'无限';

这不仅没有在文字周围设置引号,而是将命令分成两部分(第二部分无效,因为它以"NOINHERIT"开头).

这个故事的寓意:PostgreSQL"变量"实际上是用于文本扩展的宏,而不是真正的值.我确信它会派上用场,但起初它很棘手.

6> 小智..：

---

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

---

它不能在postgres中完成,但可以在PSQL客户端应用程序中完成.

7> Jasen..：

---

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

8> Craig Ringer..：

---

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

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

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