postgresql存储过程循环加判断（避免else的影响）

╰+哭是因爲堅強的太久メ 2022-11-28 10:32 123阅读 0赞

目的：要想组装成这样的json

countData:[{
        "name":"区县1"，
        “typeValue”:[{
            "name":"单位用地税收(万元/亩)"，
            “value”:"90"
            },{
            "name":"单位能耗营业收入(万元/吨标准煤)",
            "value":"20"
            },{
            "name":"单位污染物排放营业收入(万元/当量吨)",
            "value":"10"
            }]
        },{
        "name":"区县2"，
        “typeValue”:[{
            "name":"单位用地税收(万元/亩)"，
            “value”:"90"
            },{
            "name":"单位能耗营业收入(万元/吨标准煤)",
            "value":"20"
            },{
            "name":"单位污染物排放营业收入(万元/当量吨)",
            "value":"10"
            }]
        ]

第一条sql：先查询出所有的区县名称，namesql

第二条sql：查询所有的区县名称所带的值,valuesql

提前定义好：

sql1_sql text;
    sql1 record;
    sql1_ref refcursor;
    sql1_ text default '';
    
    sql2_sql text;
    sql2 record;
    sql2_ref refcursor;
    sql2_ text default '';
    
    
    open sql1_ref for execute sql1_sql;
        loop
            fetch sql1_ref into sql1;
            exit when not fount;
            str:=str||'{"name":"'||"sql1.name"||'","typeValue":[';
            open sql2_ref for execute sql2;
                loop
                    fetch sql2_ref into sql2;
                    exit when not found;
                    if sql2."name"=sql1."name" then
                       str:=str||'{"name":"'||"sql2.name"||'","value":"'||"sql2.value"||'"}';
                    end if;
                end loop
             close sql2_ref;
            str:=SUBSTRING(str0,"length"(str-1));
        str:=str||']}';
        end loop;
    close sql1_ref;
    str:=str||']}';