snippetsqlModerate

How to select number of affected rows to variable in function?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

rowsnumberfunctionaffectedhowselectvariable

Problem

create or replace function test()
returns void as $
begin
  update tbl set col1 = true where col2 = false;
  -- now I want to raise exception if update query affected more than 2 rows to rollback the update
end;
$ language plpgsql;

How I can select number of affected rows to a variable in function?

Solution

create or replace function test()
returns void as $
declare
    v_cnt numeric;
begin
    v_cnt := 0;

        update tbl set col1 = true where col2 = false;
        GET DIAGNOSTICS v_cnt = ROW_COUNT;

        if v_cnt > 1 then
           RAISE EXCEPTION 'more than one row affected --> %', v_cnt; 
        end if;
end;
$ language plpgsql;

Code Snippets

create or replace function test()
returns void as $$
declare
    v_cnt numeric;
begin
    v_cnt := 0;

        update tbl set col1 = true where col2 = false;
        GET DIAGNOSTICS v_cnt = ROW_COUNT;

        if v_cnt > 1 then
           RAISE EXCEPTION 'more than one row affected --> %', v_cnt; 
        end if;
end;
$$ language plpgsql;

Context

StackExchange Database Administrators Q#131825, answer score: 12

Revisions (0)

No revisions yet.