如何从函数返回更新的行 [英] How to return updated rows from function
本文介绍了如何从函数返回更新的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我对Postgres很陌生.我想创建一个函数(如存储过程)来更新多行并选择受影响的行.
i'm quite new to postgres. i want to create a function (like stored procedure) that updates multiple rows and selects affected rows.
这是我的声明:
CREATE or replace FUNCTION set_val(
_val character varying(100) ) --5
RETURNS setof "table_test" AS
$body$
declare results "table_test"%rowtype;
begin
update "table_test"
set "value" = $1
where "gender" = 'm'
returning * into results;
if not found then
insert into "table_test"("value")
values($1)
returning * into results;
end if;
return next results;
end;
$body$
LANGUAGE 'plpgsql' ;
只要只有1行受到影响,它就可以正常工作.但是当更多的行受到影响时,不会.
it works fine as long as only 1 row was affected. but when more rows are affected, it doesn't.
推荐答案
当PL/pgSQL函数必须返回setof时,必须使用.
When a PL/pgSQL function has to return setof it has to use "RETURN NEXT" or "RETURN QUERY".
这篇关于如何从函数返回更新的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文