MIME-Version: 1.0
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sun, 29 Sep 2024 03:07:17 +0530
Message-ID: <CAEzWdqfxakfrPqNPSohRm12mO3XyD3Cr9QxM488J8y3x0gFMqA@mail.gmail.com>
Subject: Context variable in application and trigger code
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000056006c062334c7bd"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqfxakfrPqNPSohRm12mO3XyD3Cr9QxM488J8y3x0gFMqA%40mail.gmail.com>
Precedence: bulk

--00000000000056006c062334c7bd
Content-Type: text/plain; charset="UTF-8"

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

--00000000000056006c062334c7bd
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<div>We have a trigger function called from a trigger w=
hich executes before inserting rows in the table (say TAB1). This trigger f=
unction does some conversion of code to description and persists the descri=
ption in the table in respective columns. We want to keep this trigger as l=
ight as possible as there=C2=A0will be 100&#39;s million rows inserted into=
 this table from multiple input=C2=A0sources and the conversion should only=
 happen=C2=A0 the inserts which happen from a specific input data stream. <=
br><br>There are 4-5 different input sources which will ingest data into th=
is table (some are using file based processing framework and some are using=
 other streaming technology). Say for example we want this description to o=
nly be fetched for input source - 1, but not others.=C2=A0

We don&#39;t have any such column persisted in the table which can be check=
ed for the input data stream such as this code to describe decoding can be =
made conditional.=C2=A0=C2=A0</div><div><br></div><div>Are there any techni=
ques possible in which we can set some parameter or session context variabl=
e in application code level to determine the input data source, which can t=
hen be checked within the trigger function code at the very first and thus =
will avoid querying the &quot;CODE&quot; table every time the trigger execu=
tes?</div><div><br></div><div><br></div><div><b>Existing sample trigger cod=
e:-</b><br><br>CREATE OR REPLACE FUNCTION code_to_desc( )<br>RETURNS trigge=
r<br>LANGUAGE plpgsql<br>AS $function$<br>declare code_description code.cod=
e%TYPE;<br>=C2=A0begin<br>=C2=A0 =C2=A0 =C2=A0select currency_description_t=
ext into code_description<br>=C2=A0 =C2=A0 =C2=A0from code<br>=C2=A0 =C2=A0=
 where code =3D new.code_input;</div><div><br></div><div>=C2=A0 IF FOUND <s=
pan class=3D"gmail-hljs-keyword">THEN</span>
                NEW.code_input :<span class=3D"gmail-hljs-operator">=3D</sp=
an> code_description;=C2=A0</div><div>=C2=A0            <span class=3D"gmai=
l-hljs-keyword">END</span> IF;=C2=A0=C2=A0<br><br></div><div>=C2=A0 =C2=A0 =
return NEW;<br>=C2=A0end;<br>$function$;<br><div><br></div><div>Regards</di=
v><div>Yudhi</div></div></div>

--00000000000056006c062334c7bd--