如何在 PostgreSQL 查询中声明变量

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

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

---

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) > ...;

---

你也可以在 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 或更高版本。

---

动态配置设置

您可以为此“滥用”动态配置设置：

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

---

该方案基于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 的其他查询语言可能无效。

---

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

---

我想提出对 @DarioBarrionuevo's answer 的改进，以便更简单地利用临时表。

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

---

以下是使用 PREPARE statements 的示例。您仍然不能使用 ?，但可以使用 $n 表示法：

PREPARE foo(integer) AS
    SELECT  *
    FROM    somewhere
    WHERE   something = $1;
EXECUTE foo(5);
DEALLOCATE foo;

---

在 DBeaver 中，您可以像在代码中一样在查询中使用参数，所以这将起作用：

SELECT *
FROM somewhere
WHERE something = :myvar

当您运行查询时，DBeaver 会询问您 :myvar 的值并运行查询。

---

正如您将从其他答案中收集到的那样，PostgreSQL 在直接 SQL 中没有这种机制，尽管您现在可以使用 anonymous block。但是，您可以使用公用表表达式 (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 生成单行 cte（在 Oracle 中，您需要包含 FROM DUAL）。

将 cte 与另一个表交叉连接。尽管有 CROSS JOIN 语法，但旧的逗号语法更易读。

请注意，我已经选择了日期以避免 SELECT 子句中可能出现的问题。我使用了 PostgreSQL 的较短语法，但您可以使用更正式的 CAST('1980-01-01' AS date) 来实现跨方言兼容性。

通常，您希望避免交叉连接，但由于您只交叉连接单行，这具有简单地用可变数据扩大表的效果。

在许多情况下，如果名称与其他表中的名称不冲突，则不需要包含 vars. 前缀。我把它包括在这里是为了说明这一点。

此外，您可以继续添加更多 CTE。

这也适用于所有当前版本的 MSSQL 和 MySQL，它们支持变量，以及不支持变量的 SQLite，以及支持和不支持的 Oracle。

---

这是在 postges 终端中使用普通变量的代码段。我已经用过几次了。但需要想出更好的方法。在这里，我正在使用字符串变量。使用整数变量，您不需要三引号。三引号在查询时变成单引号；否则会出现语法错误。在使用字符串变量时，可能有一种方法可以消除三引号的需要。如果您找到改进的方法，请更新。

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

select *
from covid19strain
where name = :strainname ;

---