Date: Sat, 14 Sep 2024 12:47:13 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Manual query vs trigger during data load
Message-ID: <20240914104713.jatugdwnmojwa7kf@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="sv3knygvoa22ckwe"
Content-Disposition: inline
In-Reply-To: <CAEzWdqe0VDpTi26xby+cNTMUp6goyZE1tENua08Cg-_az1mzqQ@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240914104713.jatugdwnmojwa7kf%40hjp.at>
Precedence: bulk


--sv3knygvoa22ckwe
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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=A0lookup 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

However, I agree with Rob here. It's probably better to do the
substitution in Java.

        hp


--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--sv3knygvoa22ckwe
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmblaasACgkQ8g5IURL+
KF1MUg/8C3iHtFUUiPnGrB9D/bIAb4MP3m3jVNWWeW3pPS5r1SRsQDTxNZVm682Z
/+kkTlmRcde2jYxqHYwZrIQBIqsVIynOtLst38Hn9n+lV0DLXbkw52shq1FPy7Fm
z4bJqqyMB4px5fHAjWCqn+2kYwoJy4rP22r2n7ieB5z1p6asCQYK5SNt4P635/Jf
8Y05c5IhJuzgpCYKDh6zg+3M5TandzYdE1/gAw6E8w0BCRxB1Jp9+0P6bEC7hHux
aJn0KNJ0JjisebaaeekjEOkIMLh48M7JvkzzimL/hI2Z8NXnItp2ebJicB7CxeGK
DzgesvU/P5hZUrjKeBSM9iZbt/5qVJGS3hHkFzRAJ93/TmUjLaydMgwDu7ctcDJk
mdMU4WeZSwzSiyWOoxyNj4NFsnVItd7BeUortBUkioWAfybcSYQbHGm4p0vymxAQ
um6QlEqnOJ3J9vF3/EuktU+TFLI+pi57UECgc2LoqiHR4siua7RGF5QvvAKUzl7L
ILkaK93CiDempNy7dqmytsmo3w88KImlIPF7G2+fpo6a/7VjixQE74pd+uZFePFk
lGtBA07XEhCwayRHoGmTByFnaqfGkSztxUVdDKiJ8RJb79Dvb7TzrbWrEBuB977u
FtF13oWwuDuSPdw+XQjPj4kUo1WTg9FmGzFVTlFmFLWPefrMFt8=
=QBjl
-----END PGP SIGNATURE-----

--sv3knygvoa22ckwe--