在 PostgreSQL 查询中声明一个变量

Question

在 PostgreSQL 查询中声明一个变量

sqldatabasepostgresqlpostgresql-8.3user-variables

459

我该如何声明变量以在 PostgreSQL 8.3 查询中使用？

在 MS SQL Server 中，我可以这样做：

DECLARE @myvar INT
SET @myvar = 5

SELECT *
FROM somewhere
WHERE something = @myvar

我该如何在PostgreSQL中做相同的事情？根据文档，变量仅被声明为 "name type;"，但这会给我一个语法错误：

myvar INTEGER;

有人能给我一个正确语法的例子吗？

- EMP

3

可以只使用PostgreSQL来完成。请参考以下相关问题的答案： https://dev59.com/fXRA5IYBdhLWcg3w9izq - Sean the Bean

4

此相关答案更好：https://dev59.com/V2Yr5IYBdhLWcg3w4-B- - Erwin Brandstetter

这个相关的问题有更好的答案：https://stackoverflow.com/q/13316773/939860 - undefined

17个回答

212

PostgreSQL中没有这样的功能。您只能在pl/PgSQL（或其他pl/*）中执行，而不是在普通SQL中执行。

一个例外是WITH ()查询，它可以作为变量，甚至是变量的tuple。它允许您返回临时值的表。

WITH master_user AS (
    SELECT
      login,
      registration_date
    FROM users
    WHERE ...
)

SELECT *
FROM users
WHERE master_login = (SELECT login
                      FROM master_user)
      AND (SELECT registration_date
           FROM master_user) > ...;

- user80168

3

我尝试了使用CTE作为变量的方法，但是很快遇到一个问题，就是在不同的CTE中进行数据修改查询时不能保证相互之间能够看到对方的影响。因为我需要在多个查询中使用该变量，所以我不得不使用多个CTE。 - Zia Ul Rehman Mughal

156

你也可以尝试在PLPGSQL中这样做：

DO $$
DECLARE myvar integer;
BEGIN
    SELECT 5 INTO myvar;
    
    DROP TABLE IF EXISTS tmp_table;
    CREATE TEMPORARY TABLE tmp_table AS
    SELECT * FROM yourtable WHERE   id = myvar;
END $$;

SELECT * FROM tmp_table;

需要Postgres 9.0或更高版本。

- Dario Barrionuevo

3

DO语句是在PostgreSQL 9.0版本中新增的，不支持8.3版本。 - Johny

29

请使用CREATE TEMPORARY TABLE或CREATE TEMP TABLE，而不是CREATE TABLE。其他方面都没问题。 - Stefan Steiger

112

动态配置设置

您可以通过“滥用”动态配置设置来实现此目的：

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

- user330315

4

是的，那是交易性的。 - user330315

2

作为一则附注：有些单词是保留的，例如将“set session my.vars.id = '1';”更改为“set session my.user.id = '1';”将导致“ERROR: syntax error at or near 'user'”。 - dominik

3

要使变量事务特定，您需要使用：SET LOCAL ...。session变量将在连接有效期内生效。local的作用范围限于事务。 - Eugen Konkov

@dominik 你可以通过使用引号来避免这种限制，例如：set session "my.user.id" = '1'; current_setting('my.user.id') 调用按预期工作。 - Miles Elam

执行 set_config('my.acct_id', '123', false); - Charlie 木匠

显示剩余2条评论

83

"It depends on your client."

"然而，如果您使用的是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';

- Shahriar Aghajani

1

\set 必须为小写。 - deluan

db=# \set profile_id 102 db=# :profile_id; 错误：语法错误，或者在“102”附近第1行：102; - AlxVallejo

1

@AlxVallejo 你需要在语句和 psql 控制台中使用它。db=> \set someid 8292 db=> SELECT * FROM sometable WHERE id = :someid; - everis

这个 :variable 语法也是您在 PowerQuery 中通过 Value.NativeQuery 进行带参数的本地查询所需的。 - Zezombye

49

这个解决方案基于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不同的其他查询语言可能无效。

- Jorge Luis

非常整洁，我会经常使用它。只是好奇 - 在这种情况下，TABLE关键字是做什么的？由于它是一个通用术语，所以我找不到任何有用的信息。 - user323094

@user323094，它与“select * from XX”相同。 - tcpiper

2

它只能工作一次。如果您编写选择查询以两次使用相同的值，则会出现错误，指出“SQL错误[42P01]：错误：关系“constant_1_str”不存在位置：20”。 - Satish Patro

2

@SatishPatro 是的，这是使用CTE方法的唯一缺点 - 它仅存在于创建CTE后的第一个查询中。在我看来，这个例子可能是使用CTE方法处理变量的最好版本。 - devklick

28

在pl/PgSQL之外使用临时表

除了使用建议的pl/pgsql或其他pl/*语言之外，这是我能想到的唯一可能性。

begin;
select 5::int as var into temp table myvar;
select *
  from somewhere s, myvar v
 where s.something = v.var;
commit;

- joatmon

21

我想对@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;

- bluish

很好的解决方案，可以解决DO块无法返回数据集的问题！ - CodeFarmer

在PostgreSQL 11.0上，这样的查询返回1（可能是行数），而不是tmp_table的内容。 - Ed Noepel

20

从其他答案中可以看出，PostgreSQL 在纯 SQL 中没有这种机制，不过现在可以使用匿名块。但是，您可以使用公共表达式（CTE）来实现类似的功能：

WITH vars AS (
    SELECT 5 AS myvar
)
SELECT *
FROM somewhere,vars
WHERE something = vars.myvar;

当然你可以拥有尽可能多的变量，它们也可以是派生的。例如：

WITH vars AS (
    SELECT
        '1980-01-01'::date AS start,
        '1999-12-31'::date AS end,
        (SELECT avg(height) FROM customers) AS avg_height
)
SELECT *
FROM customers,vars
WHERE (dob BETWEEN vars.start AND vars.end) AND height<vars.avg_height;

步骤如下：

使用SELECT而不使用表生成一个单行公用表表达式（在Oracle中，您需要包括FROM DUAL）。
将公用表表达式与其他表进行交叉连接。虽然有CROSS JOIN语法，但较旧的逗号语法更易读。
请注意，我已经对日期进行了强制转换，以避免SELECT子句中可能存在的问题。我使用了PostgreSQL的缩写语法，但你也可以使用更正式的CAST('1980-01-01' AS date)以实现跨方言的兼容性。

通常情况下，您应该避免使用交叉连接，但由于只是交叉连接单个行，这样做的效果就像是用可变数据扩展表格。

如果名称与其他表不冲突，则通常不需要包括vars.前缀。我这里加上它是为了明确这一点。

此外，您还可以添加更多的公用表表达式。

这在所有当前版本的MSSQL和MySQL中都有效，它们支持变量，SQLite也支持，Oracle有点支持，有点不支持。

- Manngo

1

因为这种方法只适用于单个选择块，所以被投了反对票。我们使用变量的原因是影响多行代码。 - user1034912

@user1034912 我说的是“类似”。我们使用变量的原因之一也是为了设置任意值或预先计算稍后在查询中使用的值，它们可能会在查询中多次使用。您会注意到 OP 的示例很容易使用 CTE 完成。 - Manngo

使用“JOIN cte_name ON TRUE”而不是“FROM cte_name”更好。 - Tomex Ou

19

您可以使用工具的特殊功能，例如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);

- gavenkoa

4

这个更接近可用：我将研究DBeaver是否支持列表和循环：我需要将相同的SQL应用于多个模式，列表将是要应用它们的模式。 - WestCoastProjects

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- fei0x · Accepted Answer

405

我使用了一个WITH子句来实现相同的目标，虽然它远不如上面的方法优雅，但仍然可以完成相同的事情。虽然对于这个例子来说，这样做有些过头了，而且我也不是特别推荐这样做。

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

- fei0x

5

这种方法适用于大多数需要使用变量的情况。但是，如果您想要将变量用于 LIMIT（因为 LIMIT 不能包含变量），则应按照 Shahriar Aghajani 的答案建议使用 \set。 - cimmanon

2

这对于我有一个迁移脚本并且想要导入一些关系数据时非常理想。显然，我不会知道关系数据的序列ID。 - Relequestual

4

我刚尝试了这种方法，并发现可能有更好的方式：使用JOIN myconstants ON true，这样就不需要执行子查询了。 - vektor

24

这仅适用于单个查询，您不能在事务中的多个查询之间共享WITH CTE。 - Daenyth

4

虽然这是一个古老的问题，但以下是一种变化形式：WITH constants AS (SELECT 5 AS var) SELECT * FROM somewhere CROSS JOIN constants WHERE someting=var;。由于CROSS JOIN是单行表达式，它会为真实表中所有行数据创建虚拟副本，并简化了表达式。 - Manngo

显示剩余3条评论