在选择语句中分配变量 [英] Assign variable in select statement
问题描述
我在postgres中有一张表格,如下所示:
I have a table in postgres that looks like this:
id score
1 23
2 4
3 42
4 21
在普通SQL中,我可以声明一个变量并根据选择语句的结果为其分配值:
In normal SQL I can declare a variable and assign it a value based on the result of a select statement:
declare @this_score int;
select @this_score = score from scores where id = 3;
print @test_int;
这将输出42。是否可以在postgres中以这种方式分配变量?
This outputs 42. Is it possible to assign a variable this way in postgres?
推荐答案
PostgreSQL的SQL方言不具有脚本功能;相反,它希望您使用PL / PgSQL。在这方面,它更像是Oracle,而不是Microsoft的T-SQL或MySQL。
PostgreSQL's SQL dialect isn't extended with scripting features; instead, it expects you to use PL/PgSQL. In that regard it's more like Oracle than Microsoft's T-SQL or MySQL.
使用 DO
块运行 PL / PgSQL
,或者如果需要返回值,则创建并运行一个函数。
Use a DO
block to run PL/PgSQL
, or create and run a function if you want a return value.
例如
DO
$$
DECLARE
this_score int;
BEGIN
SELECT score FROM scores WHERE id = 3 INTO this_score;
RAISE NOTICE 'this_score is: %', this_score;
END;
$$;
这篇关于在选择语句中分配变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!