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?
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;
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
Mar 10, 2016 at 13:03
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).
Mar 10, 2016 at 13:05
UPDATE ... and then GET ... ROW_COUNT is the way right. UPDATE will automatically lock the rows in current transaction?
Mar 10, 2016 at 13:11