Hi,
We have a trigger function called from a trigger which executes before inserting rows in the table (say TAB1). This trigger function does some conversion of code to description and persists the description in the table in respective columns. We want to keep this trigger as light as possible as there will be 100's million rows inserted into this table from multiple input sources and the conversion should only happen the inserts which happen from a specific input data stream.
There are 4-5 different input sources which will ingest data into this table (some are using file based processing framework and some are using other streaming technology). Say for example we want this description to only be fetched for input source - 1, but not others. We don't have any such column persisted in the table which can be checked for the input data stream such as this code to describe decoding can be made conditional.
There are 4-5 different input sources which will ingest data into this table (some are using file based processing framework and some are using other streaming technology). Say for example we want this description to only be fetched for input source - 1, but not others. We don't have any such column persisted in the table which can be checked for the input data stream such as this code to describe decoding can be made conditional.
Are there any techniques possible in which we can set some parameter or session context variable in application code level to determine the input data source, which can then be checked within the trigger function code at the very first and thus will avoid querying the "CODE" table every time the trigger executes?
Existing sample trigger code:-
CREATE OR REPLACE FUNCTION code_to_desc( )
RETURNS trigger
LANGUAGE plpgsql
AS $function$
declare code_description code.code%TYPE;
begin
select currency_description_text into code_description
from code
where code = new.code_input;
CREATE OR REPLACE FUNCTION code_to_desc( )
RETURNS trigger
LANGUAGE plpgsql
AS $function$
declare code_description code.code%TYPE;
begin
select currency_description_text into code_description
from code
where code = new.code_input;
IF FOUND THEN
NEW.code_input := code_description;
END IF;
return NEW;
end;
$function$;
end;
$function$;
Regards
Yudhi
