将多张结构相同表的数据批量插入到一张总表中

背景：pg数据库，全国各县的数据需要批量插入到一张总表中，编写以下函数分省插入数据。

CREATE OR REPLACE PROCEDURE "scxcjgpt_jc"."insert_tables_to_target"()
AS $BODY$
DECLARE
    l_column_list text;
    l_table_name text;
    l_insert_query text;
BEGIN
    -- 获取目标表的字段列表
    SELECT string_agg(column_name, ', ') INTO l_column_list
    FROM information_schema.columns
    WHERE table_schema = 'geodata' AND table_name = 'quanguo_py';
    
    -- 遍历 t11beijing 模式下的所有表名
    FOR l_table_name IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 't11beijing' AND table_name LIKE 'xian_%'
    LOOP
        -- 构建插入语句
        l_insert_query := 'INSERT INTO geodata.quanguo_py(sheng, shi, xian, xiang, cun, lin_ye_ju, lin_chang, lin_ban, xiao_ban, di_mao, po_xiang, po_wei, po_du, tu_ceng_hd, mian_ji, di_lei, qi_yuan, yu_bi_du, you_shi_sz, pingjun_xj, ld_kd, ld_cd, bhyy, bhnd, ban_x, ban_y, xzqh, banid, sheng_xz, shi_xz, xian_xz, xiang_xz, geom) SELECT sheng, substring(xian,1,4),xian, xiang, cun, lin_ye_ju, lin_chang, lin_ban, xiao_ban, di_mao, po_xiang, po_wei, po_du, tu_ceng_hd, mian_ji, di_lei, qi_yuan, yu_bi_du, you_shi_sz, pingjun_xj, ld_kd, ld_cd, bhyy, bhnd, ban_x, ban_y, xzqh, banid,substring(xzqh,1,2),substring(xzqh,1,4),substring(xzqh,1,6),substring(xzqh,1,9), geom FROM t11beijing.' || quote_ident(l_table_name);
        
        -- 执行插入语句
        EXECUTE l_insert_query;
    END LOOP;
END;
$BODY$
LANGUAGE plpgsql;