4
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?

10
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;
  • Hmm as far as I know rows may change between select count(*) and update because rows are not locked in this transaction... So even if count will return 10 there is no guarantee that exactly 10 rows will be updated. It would require to select ... for update but then I would have to make 3 queries because for update does not work with aggregate function afaik – user606521 Mar 10 '16 at 13:03
  • 1
    Once you use PL/pgSQL, there is the ROW_COUNT variable: postgresql.org/docs/9.4/static/… Furthermore, with the default isolation level (READ COMMITTED), there are chances that you count one row and then update two (the second one being committed after the SELECT succeeds). – dezso Mar 10 '16 at 13:05
  • so UPDATE ... and then GET ... ROW_COUNT is the way right. UPDATE will automatically lock the rows in current transaction? – user606521 Mar 10 '16 at 13:11
  • 1
    @user606521 yup, thats the best way. – Patrick7 Mar 10 '16 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.