我如何在PostgreSQL 8.3查询中声明一个变量?

在MS SQL Server我可以这样做:

DECLARE @myvar INT
SET @myvar = 5

SELECT *
FROM somewhere
WHERE something = @myvar

我如何在PostgreSQL做同样的事情?根据文档,变量被简单地声明为“name type;”,但这给了我一个语法错误:

myvar INTEGER;

谁能给我一个正确语法的例子吗?


当前回答

的确,没有明确的方式来声明单值变量,你能做的是

with myVar as (select "any value really")

然后,要访问存储在这个结构中的值,就必须这样做

(select * from myVar)

例如

with var as (select 123)    
... where id = (select * from var)

其他回答

动态配置设置

你可以“滥用”动态配置设置:

-- choose some prefix that is unlikely to be used by postgres
set session my.vars.id = '1';

select *
from person 
where id = current_setting('my.vars.id')::int;

配置设置始终是varchar值,因此在使用它们时需要将它们转换为正确的数据类型。这适用于任何SQL客户端,而\set只适用于psql

以上要求Postgres 9.2或更高版本。

对于以前的版本,该变量必须在使用之前在postgresql.conf中声明,因此在某种程度上限制了它的可用性。实际上不完全是变量,而是配置“类”,本质上是前缀。但是一旦定义了前缀,任何变量都可以使用而不需要改变postgresql.conf

这取决于你的客户。

然而,如果你正在使用psql客户端,那么你可以使用以下方法:

my_db=> \set myvar 5
my_db=> SELECT :myvar  + 1 AS my_var_plus_1;
 my_var_plus_1 
---------------
             6

如果你使用文本变量,你需要引用。

\set myvar 'sometextvalue'
select * from sometable where name = :'myvar';

下面是在postges终端中使用普通变量的代码段。我用过几次。但得想个更好的办法。这里我正在使用字符串变量。使用整型变量时,不需要三引号。三引号在查询时变成单引号;否则就会出现语法错误。在处理字符串变量时,可能有一种方法可以消除对三引号的需求。如果你找到了改进的方法,请及时更新。

\set strainname '''B.1.1.7'''

select *
from covid19strain
where name = :strainname ;

你也可以在PLPGSQL中尝试:

DO $$
DECLARE myvar integer;
BEGIN
    SELECT 5 INTO myvar;

    DROP TABLE IF EXISTS tmp_table;
    CREATE TABLE tmp_table AS
    SELECT * FROM yourtable WHERE   id = myvar;
END $$;

SELECT * FROM tmp_table;

以上要求Postgres 9.0或更高版本。

在psql中,你可以使用这些“变量”作为宏。请注意,它们在每次使用时都被“计算”,而不是在“设置”时。

简单的例子:

\set my_random '(SELECT random())'
select :my_random;  -- gives  0.23330629315990592
select :my_random;  -- gives  0.67458399344433542

这样每次都会给出两个不同的答案。

但是,您仍然可以使用这些有价值的简写来避免重复大量的子选择。

\set the_id '(SELECT id FROM table_1 WHERE name = ''xxx'' LIMIT 1)'

然后在以后的查询中使用它

:the_id 

e.g.

INSERT INTO table2 (table1_id,x,y,z) VALUES (:the_id, 1,2,3)

注意,你必须对变量中的字符串加双引号,因为整件事都是字符串插值(即宏展开)到你的查询中。