Hi:
I have a trigger function defined for the insert
(pasted below) for the table say "transform_item_tgt" . The
purpose of my trigger function is that before inserting each row it checks
whether value in the primary key column exists or not. The checking is
through executing the below mentioned code:
open cursorExistingRows for EXECUTE
checkPKSql;
fetch cursorExistingRows into tempRecord; here checkPKSql is the
dynamically generated query u can see that inside the function itself. If it
fetch record it means that the primary key value already exists and accordingly
i update the record instead of inserting it.
The problem is that the execution
of function is slow. As far as i think the performance degradation
is bcoz of execution of dynamically generated query for each row . As a
query run is not prepared and saved just once during
the life of the server.
Some more information
My insert query is:
insert into "transform_item_tgt" ("custom_tariffs_tgt",
"if_kit_tgt", "itm_id_tgt", "itm_name_tgt", "itm_rate_tgt", "stock_tgt")
select "item"."custom_tariffs", "item"."if_kit",
"item"."itm_id", "item"."itm_name", "item"."itm_rate",
"item"."stock" from "item";
Trigger is defned on the table "transform_item_tgt"
Please help me and suggest some solution for this
problem.
Thanks in advance.
Rajat.
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; |