MIME-Version: 1.0
References: <CAEzWdqfbZqtMuc5MD3g+iQDqN75WubeWcEvO6eDSBC_QmbKDvg@mail.gmail.com>
 <CAHbZ42zSrQAANMHaU78N9CBFYoK47kReRebijd8XS3+Oag8htA@mail.gmail.com>
 <f76b71b5-1f99-43bd-bdca-202999ea94be@aklaver.com> <67f2a664-b480-40ea-892d-2ab2147ad390@aklaver.com>
 <CAEzWdqe0VDpTi26xby+cNTMUp6goyZE1tENua08Cg-_az1mzqQ@mail.gmail.com> <20240914104713.jatugdwnmojwa7kf@hjp.at>
In-Reply-To: <20240914104713.jatugdwnmojwa7kf@hjp.at>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sat, 14 Sep 2024 21:21:45 +0530
Message-ID: <CAEzWdqe0J+OG8rgBmsfA1-JS-O8AVCq+NvxxCY-htpTrxRMZmg@mail.gmail.com>
Subject: Re: Manual query vs trigger during data load
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000baacf0062216514b"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqe0J%2BOG8rgBmsfA1-JS-O8AVCq%2BNvxxCY-htpTrxRMZmg%40mail.gmail.com>
Precedence: bulk

--000000000000baacf0062216514b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Sep 14, 2024 at 4:17=E2=80=AFPM Peter J. Holzer <hjp-pgsql@hjp.at> =
wrote:

> On 2024-09-14 00:54:49 +0530, yudhi s wrote:
> > As "thiemo" mentioned , it can be done as below method, but if we have
> > multiple lookup tables to be populated for multiple columns , then , ho=
w
> can
> > the INSERT query be tweaked to cater the need here?
>
> Just use a join:
>     insert into target(val1, val2, val3, val4)
>     select :param1, cfgA.substA, :param3, cfgB.substB
>     from cfgA, cfgB
>     where cfgA.keyA =3D :param2 and cfgB.keyB =3D :param4
>
> Or use a CTE per lookup which might be more readable:
>
>     with cA as ( select substA from cfgA where keyA =3D :param2 ),
>          cB as ( select substB from cfgB where keyB =3D :param4 )
>     insert into target(val1, val2, val3, val4)
>     select :param1, cA.substA, :param3, cB.substB
>     from cA, cB
>
>
Thank you. I will try these options.
Also we are trying to do something as below , which will separate the
tables based on the specific lookup fields for the target tables and thus
it will look simple rather than using those reference tables in the From
clause which may cause some confusion in reading the code or not sure if it
will cause cartesian. Please correct me if I'm wrong.

INSERT INTO tab_part1 (column1, column2, column3, column4, column5,
part_date)
VALUES ( :v_col1, (SELECT lookup_value FROM reference_tab1 WHERE lookup_key
=3D :v_col2), :v_col3, :v_col4, :v_col5, CURRENT_DATE );

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Sat, Sep 14, 2024 at 4:17=E2=80=AFPM P=
eter J. Holzer &lt;<a href=3D"mailto:hjp-pgsql@hjp.at">hjp-pgsql@hjp.at</a>=
&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On 2=
024-09-14 00:54:49 +0530, yudhi s wrote:<br>
&gt; As &quot;thiemo&quot; mentioned , it can be done as below method, but =
if we have<br>
&gt; multiple=C2=A0lookup tables to be populated for multiple columns , the=
n , how can<br>
&gt; the INSERT query be tweaked to cater the need here?<br>
<br>
Just use a join:<br>
=C2=A0 =C2=A0 insert into target(val1, val2, val3, val4)<br>
=C2=A0 =C2=A0 select :param1, cfgA.substA, :param3, cfgB.substB<br>
=C2=A0 =C2=A0 from cfgA, cfgB<br>
=C2=A0 =C2=A0 where cfgA.keyA =3D :param2 and cfgB.keyB =3D :param4<br>
<br>
Or use a CTE per lookup which might be more readable:<br>
<br>
=C2=A0 =C2=A0 with cA as ( select substA from cfgA where keyA =3D :param2 )=
,<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0cB as ( select substB from cfgB where key=
B =3D :param4 )<br>
=C2=A0 =C2=A0 insert into target(val1, val2, val3, val4)<br>
=C2=A0 =C2=A0 select :param1, cA.substA, :param3, cB.substB<br>
=C2=A0 =C2=A0 from cA, cB<br><br></blockquote><div><br></div><div>Thank you=
. I will try these options.=C2=A0</div><div>Also we are trying to do someth=
ing as below , which will separate the tables based on the specific=C2=A0lo=
okup fields for the target tables and thus it will look simple rather than =
using those reference tables in the From clause which may cause some confus=
ion in reading the code or not sure if it will cause cartesian. Please corr=
ect me if I&#39;m wrong.</div><div><br></div><span style=3D"color:rgb(19,19=
,19);font-family:-apple-system,BlinkMacSystemFont,&quot;Segoe UI&quot;,Robo=
to,&quot;Helvetica Neue&quot;,Arial,&quot;Apple Color Emoji&quot;,&quot;Seg=
oe UI Emoji&quot;,&quot;Segoe UI Symbol&quot;,sans-serif;font-size:14px">IN=
SERT INTO tab_part1 (column1, column2, column3, column4, column5, part_date=
)</span><br style=3D"margin-top:0px;margin-bottom:0px;color:rgb(19,19,19);f=
ont-family:-apple-system,BlinkMacSystemFont,&quot;Segoe UI&quot;,Roboto,&qu=
ot;Helvetica Neue&quot;,Arial,&quot;Apple Color Emoji&quot;,&quot;Segoe UI =
Emoji&quot;,&quot;Segoe UI Symbol&quot;,sans-serif;font-size:14px"><div><sp=
an style=3D"color:rgb(19,19,19);font-family:-apple-system,BlinkMacSystemFon=
t,&quot;Segoe UI&quot;,Roboto,&quot;Helvetica Neue&quot;,Arial,&quot;Apple =
Color Emoji&quot;,&quot;Segoe UI Emoji&quot;,&quot;Segoe UI Symbol&quot;,sa=
ns-serif;font-size:14px">VALUES ( :v_col1, (SELECT lookup_value FROM refere=
nce_tab1 WHERE lookup_key =3D :v_col2), :v_col3, :v_col4, :v_col5, CURRENT_=
DATE );</span>=C2=A0</div></div></div>

--000000000000baacf0062216514b--