postgresql 存储过程
循环插入 根据插入的值判断插入相应的字段
在PostgreSQL中，您可以使用PL/pgSQL语言编写函数，该函数可以在循环中执行插入操作，并根据插入的值判断应该插入哪些字段。但是，请注意，PostgreSQL通常不支持动态字段插入，因为表的结构是固定的。您通常需要在插入时提供所有字段的值，即使某些字段是NULL。
不过，您可以通过逻辑判断来确保只插入非NULL或符合特定条件的值。以下是一个示例函数，它使用循环来插入数据，并根据条件判断哪些字段应该被包含在INSERT语句中：
CREATE OR REPLACE FUNCTION loop_insert_based_on_value()
RETURNS void AS
$$
DECLARE
row_record RECORD; -- 用于存储从源表读取的行
should_insert_field1 BOOLEAN := false;
should_insert_field2 BOOLEAN := false;
should_insert_field3 BOOLEAN := false;
BEGIN
-- 假设我们有一个源表source_data，从中读取数据
FOR row_record IN SELECT * FROM source_data LOOP
-- 根据row_record中的值判断哪些字段应该被插入
IF row_record.some_column IS NOT NULL AND row_record.some_column > 10 THEN
should_insert_field1 := true;
END IF;
IF row_record.another_column = 'value' THEN
should_insert_field2 := true;
END IF;
-- 添加更多条件判断...
-- 构建INSERT语句，只包含需要插入的字段
INSERT INTO target_table (
field1,
field2,
field3
) VALUES (
CASE WHEN should_insert_field1 THEN row_record.value_for_field1 ELSE NULL END,
CASE WHEN should_insert_field2 THEN row_record.value_for_field2 ELSE NULL END,
CASE WHEN should_insert_field3 THEN row_record.value_for_field3 ELSE NULL END
);
-- 重置字段插入标志，为下一次循环做准备
should_insert_field1 := false;
should_insert_field2 := false;
should_insert_field3 := false;
END LOOP;
END;
$$
LANGUAGE plpgsql;
在这个函数中，我们首先声明了一些变量来跟踪哪些字段应该被插入。然后，我们使用一个FOR循环来遍历源表source_data中的每一行。对于每一行，我们检查某些条件，并设置相应的标志变量。最后，我们构建一个INSERT语句，只包括那些其标志变量为TRUE的字段。我们使用CASE语句在INSERT中为这些字段提供值，或者如果不需要插入该字段，则提供NULL。
请注意，这个示例假设source_data表中包含用于决定哪些字段应该被插入的列（如some_column和another_column），以及用于插入到target_table中的实际值（如value_for_field1，value_for_field2和value_for_field3）。您需要根据您的实际表结构和业务需求来调整这些列名。
此外，虽然这种方法可以实现基于值的字段插入，但它并不是最优的，因为它在每次循环迭代时都执行一个完整的INSERT语句。如果可能，最好优化您的数据库模式或业务逻辑，以避免这种动态字段插入的需求。
实战sql，批量新增
CREATE OR REPLACE PROCEDURE public.data_read_zao0070(p_org_id integer, p_uid character varying, p_user_id character varying)
LANGUAGE plpgsql
AS $procedure$
DECLARE
p_result_count integer;
current_row RECORD;
current_row2 RECORD;
current_rows varchar[];
current_value varchar;
-- 定义判断需要增加的字段，为true需要增加,false不需要增加。
inventory_used_field1 BOOLEAN := false;
inventory_used_amount_field2 BOOLEAN := false;
inventory_nonused_field3 BOOLEAN := false;
inventory_nonused_amount_field4 BOOLEAN := false;
-- 循环插入
FOR current_row2 IN (select
m.*
from
plant_if_sap_zao0070 m
where
not exists (
select
*
from
plant_actual p
where
m.del_flag = '0'
and p.del_flag = '0'
and trim(m.m_code) = trim(p.m_code)
and
to_char((date_trunc('MONTH', to_date(m.stock_years, 'YYYYMM') + interval '1 month') - interval '1 day')::date, 'YYYY-MM-DD')
= p.plt_shp_dt
and
to_char((date_trunc('MONTH', to_date(m.stock_years, 'YYYYMM') + interval '1 month') - interval '1 day')::date, 'YYYY-MM-DD')
= p.shipping_dt)
and exists (
select
*
from
plant_m_model mo
where
mo.del_flag = '0'
and m.del_flag = '0'
and trim(mo.m_code) = trim(m.m_code))
and m.org_id = p_org_id) LOOP
IF trim(current_row2.warehouse_code) = 'BLK' THEN
inventory_nonused_field3 := true;
inventory_nonused_amount_field4 := true;
END IF;
IF trim(current_row2.warehouse_code) = 'URG' THEN
inventory_used_field1 := true;
inventory_used_amount_field2 := true;
END IF;
raise notice '++++++++++ ';
raise notice '标记: [%] ',inventory_nonused_field3;
raise notice '标记: [%] ',inventory_nonused_amount_field4;
raise notice '标记: [%] ',inventory_used_field1;
raise notice '标记: [%] ',inventory_used_amount_field2;
raise notice '++++++++++ ';
INSERT INTO public.plant_actual
(
org_id, m_code, bo_code, plt_shp_dt, shipping_dt, currency,
inventory_used, inventory_used_amount, inventory_nonused, inventory_nonused_amount,
create_by, modify_by
)values(
p_org_id,
trim(current_row2.m_code),
null,
to_char((date_trunc('MONTH', to_date(current_row2.stock_years, 'YYYYMM') + interval '1 month') - interval '1 day')::date, 'YYYY-MM-DD'),
to_char((date_trunc('MONTH', to_date(current_row2.stock_years, 'YYYYMM') + interval '1 month') - interval '1 day')::date, 'YYYY-MM-DD'),
'CNY',
CASE WHEN inventory_used_field1 THEN CAST(CONCAT(current_row2.q_qty_sign,current_row2.q_qty_number) AS NUMERIC) ELSE NULL END,
CASE WHEN inventory_used_amount_field2 THEN CAST(CONCAT(current_row2.q_amount_sign,current_row2.q_amount_number, '.', current_row2.q_amount_point) AS NUMERIC) ELSE NULL END,
CASE WHEN inventory_nonused_field3 THEN CAST(CONCAT(current_row2.r_qty_sign,current_row2.r_qty_number) AS NUMERIC) ELSE NULL END,
CASE WHEN inventory_nonused_amount_field4 THEN CAST(CONCAT(current_row2.r_amount_sign,current_row2.r_amount_number, '.', current_row2.r_amount_point) AS NUMERIC) ELSE NULL END,
p_user_id,
p_user_id
);
-- 循环结束 重置变量
inventory_used_field1 := false;
inventory_used_amount_field2 := false;
inventory_nonused_field3 := false;
inventory_nonused_amount_field4 := false;
END LOOP;
实战sql，批量更新 具体逻辑 思路仿照上面新增的思路
CREATE OR REPLACE PROCEDURE public.data_read_zao0070(p_org_id integer, p_uid character varying, p_user_id character varying)
LANGUAGE plpgsql
AS $procedure$
DECLARE
p_result_count integer;
current_row RECORD;
current_row2 RECORD;
current_rows varchar[];
current_value varchar;
inventory_used_field1 BOOLEAN := false;
inventory_used_amount_field2 BOOLEAN := false;
inventory_nonused_field3 BOOLEAN := false;
inventory_nonused_amount_field4 BOOLEAN := false;
FOR current_row IN (
select
m.*
from
plant_if_sap_zao0070 m
where
exists (
select
*
from
plant_actual p
where
m.del_flag = '0'
and p.del_flag = '0'
and trim(m.m_code) = trim(p.m_code)
and p.plt_shp_dt =
to_char((date_trunc('MONTH', to_date(m.stock_years, 'YYYYMM') + interval '1 month') - interval '1 day')::date, 'YYYY-MM-DD')
and p.shipping_dt =
to_char((date_trunc('MONTH', to_date(m.stock_years, 'YYYYMM') + interval '1 month') - interval '1 day')::date, 'YYYY-MM-DD')
)
and exists (
select
*
from
plant_m_model mo
where
mo.del_flag = '0'
and m.del_flag = '0'
and trim(mo.m_code) = trim(m.m_code))
and m.org_id = p_org_id
) LOOP
raise notice '更改数据 : [%] ',current_row;
IF trim(current_row.warehouse_code) = 'BLK' THEN
inventory_nonused_field3 := true;
inventory_nonused_amount_field4 := true;
END IF;
IF trim(current_row.warehouse_code) = 'URG' THEN
inventory_used_field1 := true;
inventory_used_amount_field2 := true;
END IF;
raise notice '--------- ';
raise notice '标记: [%] ',inventory_nonused_field3;
raise notice '标记: [%] ',inventory_nonused_amount_field4;
raise notice '标记: [%] ',inventory_used_field1;
raise notice '标记: [%] ',inventory_used_amount_field2;
raise notice '--------- ';
UPDATE public.plant_actual
SET
inventory_used=
CASE WHEN inventory_used_field1 THEN
CAST(CONCAT(current_row.q_qty_sign,current_row.q_qty_number) AS NUMERIC) ELSE NULL END,
inventory_used_amount=
CASE WHEN inventory_used_amount_field2 THEN CAST(CONCAT(current_row.q_amount_sign,current_row.q_amount_number, '.', current_row.q_amount_point) AS NUMERIC) ELSE NULL END,
inventory_nonused=
CASE WHEN inventory_nonused_field3 THEN CAST(CONCAT(current_row.r_qty_sign,current_row.r_qty_number) AS NUMERIC) ELSE NULL END,
inventory_nonused_amount=
CASE WHEN inventory_nonused_amount_field4 THEN CAST(CONCAT(current_row.r_amount_sign,current_row.r_amount_number, '.', current_row.r_amount_point) AS NUMERIC) ELSE NULL END,
modify_by=p_user_id
WHERE trim(m_code)=trim(current_row.m_code)
AND plt_shp_dt =
to_char((date_trunc('MONTH', to_date(current_row.stock_years, 'YYYYMM') + interval '1 month') - interval '1 day')::date, 'YYYY-MM-DD')
and shipping_dt =
to_char((date_trunc('MONTH', to_date(current_row.stock_years, 'YYYYMM') + interval '1 month') - interval '1 day')::date, 'YYYY-MM-DD')
and del_flag='0' and org_id=p_org_id;
-- 增加一步修改状态为1
update plant_if_sap_zao0070 set del_flag = '1'
where trim(m_code) = trim(current_row.m_code) and org_id = p_org_id;
inventory_used_field1 := false;
inventory_used_amount_field2 := false;
inventory_nonused_field3 := false;
inventory_nonused_amount_field4 := false;
END LOOP;
