Hi:

The below pasted trigger function is executing slow

Please refer to the lines of the code in BOLD commented as performance degradation.

The same code I have mentioned below also:

open cursorExistingRows for EXECUTE checkPKSql;
fetch cursorExistingRows into tempRecord;

When I commented out this statement, the function output is fast when i tested for more than 1000 records otherwise
its take almost double time.

Please tell me how to solve this problem.

CREATE FUNCTION "public"."transform_item_tgtinsertupdate" () RETURNS trigger AS'

declare
cursorExistingRows refcursor;
tempRecord record;
updateSql varchar;
checkPKSql varchar;

begin
updateSql = 'UPDATE transform_item_tgt set ';
IF NEW.itm_id_tgt is not null then
updateSql := updateSql || ' itm_id_tgt = ' || quote_literal(NEW.itm_id_tgt) || ', ';
END IF;
IF NEW.custom_tariffs_tgt is not null then
updateSql := updateSql || ' custom_tariffs_tgt = ' || quote_literal(NEW.custom_tariffs_tgt) || ', ';
END IF;
IF NEW.if_kit_tgt is not null then
updateSql := updateSql || ' if_kit_tgt = ' || quote_literal(NEW.if_kit_tgt) || ', ';
END IF;
IF NEW.itm_name_tgt is not null then
updateSql := updateSql || ' itm_name_tgt = ' || quote_literal(NEW.itm_name_tgt) || ', ';
END IF;
IF NEW.itm_price_unit_tgt is not null then
updateSql := updateSql || ' itm_price_unit_tgt = ' || quote_literal(NEW.itm_price_unit_tgt) || ', ';
END IF;
IF NEW.itm_rate_tgt is not null then
updateSql := updateSql || ' itm_rate_tgt = ' || quote_literal(NEW.itm_rate_tgt) || ', ';
END IF;
IF NEW.itm_type_tgt is not null then
updateSql := updateSql || ' itm_type_tgt = ' || quote_literal(NEW.itm_type_tgt) || ', ';
END IF;
IF NEW.itm_unit_tgt is not null then
updateSql := updateSql || ' itm_unit_tgt = ' || quote_literal(NEW.itm_unit_tgt) || ', ';
END IF;
IF NEW.status_tgt is not null then
updateSql := updateSql || ' status_tgt = ' || quote_literal(NEW.status_tgt) || ', ';
END IF;
IF NEW.stock_tgt is not null then
updateSql := updateSql || ' stock_tgt = ' || quote_literal(NEW.stock_tgt) || ', ';
END IF;

updateSql := substring(updateSql, 0, length(updateSql)-1);
checkPKSql := 'select itm_id_tgt from transform_item_tgt ';
updateSql := updateSql || ' where itm_id_tgt = ' || quote_literal(new.itm_id_tgt);
checkPKSql := checkPKSql || ' where itm_id_tgt = ' || quote_literal(new.itm_id_tgt);
--Raise notice 'the query is: %', checkPKSql;

----------------------PERFORMANCE DEGRADATION--------------------------
open cursorExistingRows for EXECUTE checkPKSql;
fetch cursorExistingRows into tempRecord;
----------------------PERFORMANCE DEGRADATION--------------------------

if FOUND then
execute updateSql;
close cursorExistingRows;
return null;
else
close cursorExistingRows;
return new;
end if;

close cursorExistingRows;

end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;