Re: Manual query vs trigger during data load

On 2024-09-14 21:21:45 +0530, yudhi s wrote:
> 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.

My examples do form a cartesian product, but as long as the keys are
unique, that's 1 * 1 * 1 ... * 1 = 1 rows. So that should not be a
problem in case of simple lookup tables.

That may not be immediately apparent to someone reading the code,
though. And it might fail horribly if the lookups aren't guaranteed to
return a single row.

> 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 ); 

Your approach is safer in that it will abort with an error if the
subquery ever returns more than one value. It will also still insert a
row (with null in column2) if the subquery returns no rows, which may or
may not be what you want (and if you don't want it you can probably
prevent it with a not null constraint). Looks good to me.

hp

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