PostgreSQL ERROR: cannot begin/end transactions in PL/pgSQL

Cannot begin/end transactions in PL/pgSQL

Rollback

The cause of error: Cannot begin/end transactions in PL/pgSQL is the rollback command.
The solution is to use exception clause in the function.

Wrong function

CREATE OR REPLACE FUNCTION update_test
(p_old_name varchar, p_new_name varchar)
RETURNS varchar AS $$
DECLARE
 v_result varchar(250);
 v_count numeric:=0;
BEGIN
SELECT count(*) into v_count 
FROM test WHERE name = p_old_name;
if v_count > 1 then
 rollback; 
else 
 update test set name = p_new_name 
 where name = p_old_name;
end if;
return v_result;
END;
$$ LANGUAGE plpgsql;
select update_test('name_1', 'name_3');

ERROR: cannot begin/end
transactions in PL/pgSQL

HINT: use a begin block with
an exception clause instead.

Correct function

CREATE OR REPLACE FUNCTION update_test
(p_old_name varchar, p_new_name varchar)
RETURNS varchar AS $$
DECLARE
 v_result varchar(250);
 v_count numeric:=0;
BEGIN
SELECT count(*) into v_count 
FROM test WHERE name = p_old_name;
if v_count > 1 then
 rollback; 
else 
 update test set name = p_new_name 
 where name = p_old_name;
end if;
return v_result;

exception when others then
begin
v_result:='DBA ERROR';
return v_result;
end;
END;
$$ LANGUAGE plpgsql;
select update_test('name_1', 'name_3');

Data Output: DBA ERROR