Postgresql 将选择查询分配给函数中的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19267048/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql assign a select query to variable in the function
提问by Xianlin
I am using Postgresql 9.3 and wrote a function as below:
我正在使用 Postgresql 9.3 并编写了一个函数，如下所示：
create or replace function test(building text,floor text) returns void as $$
Declare
id integer;
num integer := 0;
Begin
num=num+100
id :=select to_number(
(select
(select code from buildings where name=building) || floor
|| (select num::text)),'99999999'
);
update table set col1=id;
End;
$$
language plpgsql;
What I expect is that my idvariable will be assigned a number value example: 12502100from the select to_number(...)query.
我想到的是，我的id变量将被分配一个数值example: 12502100从select to_number(...)查询。
However I got the below error
但是我收到以下错误
ERROR: syntax error at or near ":="
LINE 10: source :=(select code from buildings where name='I3')
How can I assign the query result (with some string operations) into the variable id?
如何将查询结果（带有一些字符串操作）分配到变量 id 中？
I am also failed with Select Into id...method.
我的Select Into id...方法也失败了。
采纳答案by Pavel Stehule
You don't need use SELECTfor function evaluation.
您不需要SELECT用于函数评估。
id := to_number((SELECT code FROM buildings WHERE name = building)
|| floor || num::text,
'999999999');
other possibility (and usually better) is using function in expression list (result field list)
另一种可能性（通常更好）是在表达式列表（结果字段列表）中使用函数
id := (SELECT to_number(code || floor || num::text, '99999999')
FROM buildings WHERE name = building)
Use SELECTonly when you need a query to data, not for function or variable evaluation!
使用SELECT只有当你需要查询数据，而不是函数或变量的评价！
