如何从函数返回更新的行 [英] 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屋！