Slow execution of the function due to the execution of dynamically generated queries defined inside it - Mailing list pgsql-general

From Rajat Katyal
Subject Slow execution of the function due to the execution of dynamically generated queries defined inside it
Date
Msg-id 006c01c40db0$5a220280$2105a8c0@coffee
Whole thread Raw
In response to  ("Rajat Katyal" <rajatk@intelesoftech.com>)
List pgsql-general
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;
 

pgsql-general by date:

Previous
From: "pgsql-general@list.coretech.ro"
Date:
Subject: trigger statement
Next
From: Stephen Frost
Date:
Subject: Re: "People near me" query