On Sat, Sep 14, 2024 at 4:17 PM Peter J. Holzer <hjp-pgsql@hjp.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 );
