PG通过匿名

一、背景

1、需要基于一组数据，同步N组相类似的配置数据，每次升级都需要手动一组组配置，通过PG
的循环进行处理

二、代码

知识点补充：

1、DO关键字用来执行一段匿名代码块，即在在程序语言过程中一次性执行的匿名函数

2、创建序列变量，保证id不重复scene_info_id_seq

3、自动递增函数：nextval('scene_info_id_seq')

4、实例：

-- ======================================
-- ======================================
DO
$$
    DECLARE
        hospital_temp          varchar := 'H33010902127';
        insurance_companies    text[]  := ARRAY ['PICC000000',
            'HAIC000000',
            'PICC000000',
            'GPIC000000',
            ]; -- 初始化一组数据
        insurance_company_temp varchar;
    BEGIN

        FOREACH insurance_company_temp IN ARRAY insurance_companies
            LOOP

                delete
                from data_gateway.scene_info t
                where t.insurance_company_code = insurance_company_temp
                  and hospital_code = hospital_temp;
                delete
                from data_gateway.mode_process t
                where t.insurance_company_code = insurance_company_temp
                  and hospital_code = hospital_temp;

            END LOOP;


        FOREACH insurance_company_temp IN ARRAY insurance_companies
            LOOP
                --添加保司任务
                insert into data_gateway.scene_info(id, hospital_code, insurance_company_code, scene_code, dataset_code,
                                                    medical_category)
                select nextval('scene_info_id_seq') + 500,
                       hospital_code,
                       insurance_company_temp,
                       scene_code,
                       dataset_code,
                       medical_category
                from data_gateway.scene_info t
                where t.hospital_code = hospital_temp
                  and t.insurance_company_code = 'HHHHH0001';


                --调用kettle
                insert into data_gateway.mode_process(id, hospital_code, insurance_company_code, scene, process, type,
                                                      medical_category)
                select nextval('mode_process_id_seq') + 500,
                       hospital_code,
                       insurance_company_temp,
                       scene,
                       process,
                       type,
                       medical_category
                from data_gateway.mode_process t
                where t.hospital_code = hospital_temp
                  and t.insurance_company_code = 'HHHHH0001';

            END LOOP;
    END
$$;

三，参考

1、PostgreSQL - pl/pgsql的DO关键字-腾讯云开发者社区-腾讯云 (tencent.com)

2、PostgreSQL数据库PL/PGSQL学习使用 - wangzhen3798 - 博客园 (cnblogs.com)