我如何在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)

其他回答

您可以求助于工具的特殊功能。比如DBeaver自己的专有语法:

@set name = 'me'
SELECT :name;
SELECT ${name};

DELETE FROM book b
WHERE b.author_id IN (SELECT a.id FROM author AS a WHERE a.name = :name);

动态配置设置

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

-- 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

此解决方案基于fei0x提出的解决方案,但它的优点是不需要在查询中加入常量的值列表,并且可以在查询开始时轻松列出常量。它也适用于递归查询。

基本上,每个常量都是在WITH子句中声明的单值表,然后可以在查询的其余部分的任何地方调用它。

包含两个常量的基本示例:

WITH
    constant_1_str AS (VALUES ('Hello World')),
    constant_2_int AS (VALUES (100))
SELECT *
FROM some_table
WHERE table_column = (table constant_1_str)
LIMIT (table constant_2_int)

或者,你可以使用SELECT * FROM constant_name代替TABLE constant_name,这对于与postgresql不同的其他查询语言可能无效。

我想对@DarioBarrionuevo的回答提出一个改进,使其更简单地利用临时表。

DO $$
    DECLARE myvar integer = 5;
BEGIN
    CREATE TEMP TABLE tmp_table ON COMMIT DROP AS
        -- put here your query with variables:
        SELECT * 
        FROM yourtable
        WHERE id = myvar;
END $$;

SELECT * FROM tmp_table;

我通过使用WITH子句实现了同样的目标,它远没有那么优雅,但可以做同样的事情。虽然对于这个例子来说,它真的是太夸张了。我也不特别推荐这个。

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;