我期望的是我的 id 变量将从中选择to_number（...）查询，为其分配一个数值例如：12502100 。
然而，我得到了以下错误：
错误：语法错误在或接近：= LINE 10：source：=（从名称='I3'的建筑物中选择代码）如何将查询结果（包含一些字符串操作）分配给变量id？
我也失败了 Select Into id ... method。
解决方案您不需要使用<$ c $ p $ id：= to_number（（SELECT code FROM buildings WHERE name = building） || floor || num :: text，'999999999'）;
其他可能性（通常更好）是在表达式列表中使用函数（结果字段列表）
id：=（SELECT to_number（code || floor || num :: text，'99999999'） FROM WHERE name = building）仅当使用 SELECT 时你需要查询数据，而不是函数或变量评估！
I am using Postgresql 9.3 and wrote a function as below:
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 id variable will be assigned a number value example: 12502100 from the select to_number(...) query.
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?
I am also failed with Select Into id... method.
解决方案You don't need use SELECT for function evaluation.
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 SELECT only when you need a query to data, not for function or variable evaluation!
更多推荐
Postgresql为函数中的变量分配一个select查询
发布评论