我期望的是我的 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查询
发布评论