在 PostgreSQL 查询中声明变量

Question

如何声明在 PostgreSQL 8.3 查询中使用的变量？

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

DECLARE @myvar INT;
SET @myvar = 5/
    
SELECT * FROM somewhere WHERE something = @myvar;

How do I do the same in PostgreSQL?根据文档，变量被简单地声明为“名称类型；”，但这给了我一个语法错误：

myvar INTEGER;

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

Answer 1

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

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

Answer 2

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

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

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

Answer 3

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

Answer 4

动态配置设置

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

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

Answer 5

这取决于您的客户。

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

Answer 6

该方案基于 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 Con​​stant_name，这对于不同于 postgresql 的其他查询语言可能无效。

Answer 7

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

Answer 8

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

Answer 9

正如您从其他答案中了解到的，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 生成单行 cte，无需表（在 Oracle 中，您需要包含 FROM DUAL）。
• CROSS JOIN cte 与另一个表。尽管有 CROSS JOIN 语法，但较旧的逗号语法可读性稍好一些。
• 请注意，我已转换日期以避免 SELECT 子句中可能出现的问题。我使用了 PostgreSQL 的较短语法，但您可以使用更正式的 CAST('1980-01-01' AS date) 来实现跨方言兼容性。

通常，您希望避免交叉联接，但由于您仅交叉联接单行，因此这会产生简单地使用可变数据扩大表的效果。

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

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

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

Answer 10

您可以诉诸工具的特殊功能。就像 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);

Answer 11

确实，没有生动且明确的方法来声明单值变量，您可以做的是

with myVar as (select "any value really")

，要访问此构造中存储的值，您可以

(select * from myVar)

这样做

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

Answer 12

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

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

Answer 13

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

SELECT *
FROM somewhere
WHERE something = :myvar

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

Answer 14

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

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

select *
from covid19strain
where name = :strainname ;

Answer 15

在 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 

例如

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

请注意，您必须对变量中的字符串加双引号，因为整个内容都会被字符串插值（即宏扩展）到您的查询中。

Answer 16

例如，您可以设置 自定义选项 my.num 和 my.first.name 作为变量，如下所示。 *一个自定义选项可以有多个.，并且自定义选项在注销后将被删除：

SET my.num = 2;
SET my.first.name = 'John';

或者，您可以使用set_config() 如下所示。 *对于 set_config()，第三个参数中的 false 表示新值适用于当前会话并且 <第三个参数中的 code>true 表示新值适用于当前交易并且我的回答详细解释了set_config()：

SELECT set_config('my.num', '2', false);
SELECT set_config('my.first.name', 'John', false);

那么，你必须使用current_setting() 获取自定义选项的值 my.num< /code> 和 my.first.name 如下所示：

postgres=# SELECT current_setting('my.num');
 current_setting
-----------------
 2
(1 row)

postgres=# SELECT current_setting('my.first.name');
 current_setting
-----------------
 John
(1 row)

小心，设置自定义选项时不使用 . 获取 错误如下所示：

postgres=# SET num = 2;
ERROR:  unrecognized configuration parameter "num"

或者：

postgres=# SELECT set_config('num', '2', false);
ERROR:  unrecognized configuration parameter "num"

并且，2 中没有 '' set_config() 获取 错误如下所示：

postgres=# SELECT set_config('my.num', 2, false);
ERROR:  function set_config(unknown, integer, boolean) does not exist
LINE 1: SELECT set_config('my.num', 2, false);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

并且，使用不带current_setting()的自定义选项无法获取如下所示的值：

postgres=# SELECT 'my.num';
 ?column?
----------
 my.num
(1 row)

并且，使用不带''的自定义选项并且 current_setting() 得到错误 如下图：

postgres=# SELECT my.num;
ERROR:  missing FROM-clause entry for table "my"
LINE 1: SELECT my.num;

接下来例如可以使用 \set 将数字 2 设置为 num ，如下所示。 *您必须根据文档 否则会出现错误，num 会在注销后被删除：

postgres=# \set num 2
postgres=# SELECT :num;
 ?column?
----------
        2
(1 row)

并且，您可以使用 \set将带有 '' 的文本 John Smith 设置为 name，如下所示。 *请勿将 "" 用于 John Smith，因为 "" 包含在文本中，因此输出为 "John Smith" 并且您必须使用 '' 作为 name，否则会出现错误，并且 name 在注销后会被删除：

postgres=# \set name 'John Smith'
postgres=# SELECT :'name';
  ?column?
------------
 John Smith
(1 row)

设置文本时要小心John Smith 不带 '' 到 name 会删除 John 和 Smith 之间的空格，如下所示如下所示：

postgres=# \set name John Smith
postgres=# SELECT :'name';
 ?column?
-----------
 JohnSmith
(1 row)

并且，仅使用 \set 即可显示所有变量，如下所示：

postgres=# \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
...
name = 'John Smith'
num = '2'

并且，您可以使用 \unset 取消设置（删除）num 和 name 作为如下所示：

postgres=# \unset num
postgres=# \unset name

另外，您可以使用 \echo 输出 num 如下所示：

postgres=# \set num 2
postgres=# \echo :num
2

并且，您可以使用 \echo 输出 name 如下所示。 *请勿使用 '' 作为 name，因为 '' 包含在文本中，因此输出为 'John Smith'< /代码>：

postgres=# \set name 'John Smith'
postgres=# \echo :name
John Smith

Answer 17

使用fei0x的答案和bluish的建议 您可以将它们组合起来将您的值存储在 TEMP 表中。如果有人想使用日期/时间戳保存到变量中，以下是一个片段。

drop table if exists tempdb_var;
CREATE TEMP TABLE tempdb_var as
with varInit (v_startdate, v_enddate) as (
    values(CURRENT_DATE - '1 day'::interval, CURRENT_DATE + '1 day'::interval)

)
select a.* from varInit a;

select v_startdate,v_enddate from tempdb_var;

您可以在任何查询中使用变量 v_startdate 和 v_enddate。

Answer 18

在简单查询中（Postgress）

WITH variables AS ( SELECT 11 AS offerId)

select * from offer_config,variables where offer_id = offerId