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.
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;
IF FOUND THEN NEW.code_input := code_description;
END IF;
return NEW;
end;
$function$;
Regards
Yudhi
