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