如何在PostgreSQL查询中声明变量

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

在MS SQL Server我可以这样做:

DECLARE @myvar INT
SET @myvar = 5


SELECT *
FROM somewhere
WHERE something = @myvar

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

myvar INTEGER;

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

637354 次浏览
小开
最佳答案

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

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

你也可以在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客户端，那么你可以使用以下方法:

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

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

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

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

动态配置设置

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

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

小开

下面是一个使用准备语句的例子。你仍然不能使用?，但你可以使用$n符号:

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

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

小开

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

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

在DBeaver中，你可以在查询中使用参数，就像在代码中一样，所以这是可以工作的:

SELECT *
FROM somewhere
WHERE something = :myvar

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

小开

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

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


select *
from covid19strain
where name = :strainname ;
小开

正如你将从其他答案中收集到的，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)。
  • 交叉连接cte与另一个表。虽然有CROSS JOIN语法，但旧的逗号语法可读性稍好。
  • 注意，我转换日期是为了避免SELECT子句中可能出现的问题。我使用了PostgreSQL更短的语法，但你可以使用更正式的CAST('1980-01-01' AS date)来实现跨方言兼容性。

通常，您希望避免交叉连接，但由于您只是交叉连接单行，因此这只会使用变量数据扩大表。

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

另外，你可以继续添加更多的cte。

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

小开

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

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


提交答案