Re: Manual query vs trigger during data load

Hello, I find it unlikely that the trigger will work properly, since the
reserved fields of the OLD subset have no value in an INSERT

Atte
JRBN

El vie, 13 de sept de 2024, 04:32, yudhi s <learnerdatabase99(at)gmail(dot)com>
escribió:

> Hello All,
>
> We are having a table which is going to be inserted with 100's of millions
> of rows each day. And we now want to have a requirement in which we need to
> do some transformation/lookup logic built on top of a few of the input bind
> values , while inserting the data. So I wanted to understand ,is it
> possible to do it along with the INSERT query or is it better to have a
> trigger created for the same?
>
> For. e.g Below is the current Insert query used in the Java code. We want
> to fetch the value for "column2" from a lookup table rather than directly
> inserting as it's coming from the customer side. So I am thinking of a
> trigger like below. But at the same time I also want to compare the
> performance of a normal way of doing the lookup vs having it performed
> using triggers.
>
> So one way i am thinking is first fetching the value of the "column2" from
> reference_tab1 using a separate "select query" in Java code itself, and
> then passing that to the below insert query, but i think that will increase
> the response time as that will be a separate DB call.
>
> 1)So, is there a way I can do it directly using the single INSERT query
> itself without additional SELECT query? And then will try to compare that
> with the trigger based approach.
> 2)Additionally , if this decision will impact a batch insert approach.
> i.e. say , in case of trigger , will the batch insert fail because
> trigger will force it to make it row by row?
>
> INSERT INTO tab_part1 (column1, column2, column3, column4, column5,
> part_date)
> VALUES (:v_col1, :v_col2, :v_col3, :v_col3, :v_col4,:v_col5,
> CURRENT_DATE);
>
> CREATE OR REPLACE FUNCTION trg_func_populate_column2() RETURNS TRIGGER AS
> $$
> BEGIN
> -- Fetch reference value and populate column2
> NEW.column2 := (SELECT lookup_key FROM reference_tab1 WHERE lookup_key
> = old.column2);
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> Regards
> Yudhi
>