Re: Manual query vs trigger during data load

On Sat, Sep 14, 2024 at 4:17 PM Peter J. Holzer <hjp-pgsql(at)hjp(dot)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 , how
> 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 = :param2 and cfgB.keyB = :param4
>
> Or use a CTE per lookup which might be more readable:
>
> with cA as ( select substA from cfgA where keyA = :param2 ),
> cB as ( select substB from cfgB where keyB = :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
= :v_col2), :v_col3, :v_col4, :v_col5, CURRENT_DATE );