我该如何声明变量以在 PostgreSQL 8.3 查询中使用?
在 MS SQL Server 中,我可以这样做:
DECLARE @myvar INT
SET @myvar = 5
SELECT *
FROM somewhere
WHERE something = @myvar
我该如何在PostgreSQL中做相同的事情?根据文档,变量仅被声明为 "name type;",但这会给我一个语法错误:
myvar INTEGER;
有人能给我一个正确语法的例子吗?
我该如何声明变量以在 PostgreSQL 8.3 查询中使用?
在 MS SQL Server 中,我可以这样做:
DECLARE @myvar INT
SET @myvar = 5
SELECT *
FROM somewhere
WHERE something = @myvar
我该如何在PostgreSQL中做相同的事情?根据文档,变量仅被声明为 "name type;",但这会给我一个语法错误:
myvar INTEGER;
有人能给我一个正确语法的例子吗?
我使用了一个WITH
子句来实现相同的目标,虽然它远不如上面的方法优雅,但仍然可以完成相同的事情。虽然对于这个例子来说,这样做有些过头了,而且我也不是特别推荐这样做。
WITH myconstants (var1, var2) as (
values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
OR something_else = var2;
\set
。 - cimmanonJOIN myconstants ON true
,这样就不需要执行子查询了。 - vektorWITH
CTE。 - DaenythWITH constants AS (SELECT 5 AS var) SELECT * FROM somewhere CROSS JOIN constants WHERE someting=var;
。由于CROSS JOIN是单行表达式,它会为真实表中所有行数据创建虚拟副本,并简化了表达式。 - ManngoPostgreSQL中没有这样的功能。您只能在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) > ...;
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;
您可以通过“滥用”动态配置设置来实现此目的:
-- 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
。
SET LOCAL ...
。session
变量将在连接有效期内生效。local
的作用范围限于事务。 - Eugen Konkovset session "my.user.id" = '1';
current_setting('my.user.id')
调用按预期工作。 - Miles Elammy_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';
\set
必须为小写。 - deluandb=> \set someid 8292 db=> SELECT * FROM sometable WHERE id = :someid;
- everis:variable
语法也是您在 PowerQuery 中通过 Value.NativeQuery
进行带参数的本地查询所需的。 - Zezombye这个解决方案基于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/*语言之外,这是我能想到的唯一可能性。
begin;
select 5::int as var into temp table myvar;
select *
from somewhere s, myvar v
where s.something = v.var;
commit;
我想对@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;
1
(可能是行数),而不是tmp_table
的内容。 - Ed Noepel从其他答案中可以看出,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有点支持,有点不支持。
您可以使用工具的特殊功能,例如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);