CODEWITHSUNDEEP
postgresqlplpgsql
Bonshington
Bonshington

Reputation: 4032

How to return updated rows from function

i'm quite new to postgres. i want to create a function (like stored procedure) that updates multiple rows and selects affected rows.

here is my statement:

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' ;

it works fine as long as only 1 row was affected. but when more rows are affected, it doesn't.

Upvotes: 6

Views: 8741

Answers (2)

Bonshington
Bonshington

Reputation: 4032

i finally got it i use for loop with return next. thanks

here's my code

declare result table1%rowtype;
begin
 for result in update table1 set ... where ... returning * loop
  return next result;
 end loop;
end;

Upvotes: 2

Milen A. Radev
Milen A. Radev

Reputation: 62613

When a PL/pgSQL function has to return setof it has to use "RETURN NEXT" or "RETURN QUERY".

Upvotes: 6

Related Questions