如何在psql中使用脚本变量? [英] How do you use script variables in psql?

问题描述

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

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

DECLARE @somevariable int  
SELECT @somevariable = -1

INSERT INTO foo VALUES ( @somevariable )

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

I'll then change the value of @somevariable at 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.

如何使用PostgreSQL客户端psql进行相同操作?

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

推荐答案

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

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

\set myvariable value

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

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

SELECT * FROM :myvariable.table1;

...或...

SELECT * FROM table1 WHERE :myvariable IS NULL;

自psql 9.1起，变量可以用引号引起来，如:

\set myvariable value 

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

在旧版本的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;

这篇关于如何在psql中使用脚本变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！