如何在PostgreSQL中循环JSON的ID属性并按需执行函数

问题分析 #

原函数存在以下问题：

• 错误尝试解析不存在的arg_t键，传入的JSON参数是单个对象，无该键；
• 使用json_array_elements处理非数组类型会直接报错；
• 声明返回setof json但无任何返回语句，无法输出结果；
• 存在未使用的冗余变量i。

可行实现方案 #

根据需求，我们针对两种常见输入场景给出实现：单个带ID的JSON对象、包含多个带ID对象的JSON数组，遍历每个ID并执行对应逻辑，同时正确返回结果。

方案1：兼容单个对象/数组输入，遍历ID并返回对应JSON对象 #

CREATE OR REPLACE FUNCTION parse_json(arg_t text) 
RETURNS setof json
AS $$
DECLARE
    json_data json;
    item json;
BEGIN
    -- 将输入文本转为JSON类型
    json_data := arg_t::json;
    
    -- 统一处理：如果是单个对象，转为单元素数组；如果是数组直接使用
    IF json_typeof(json_data) = 'object' THEN
        json_data := json_build_array(json_data);
    END IF;
    
    -- 遍历数组中的每个对象
    FOR item IN SELECT * FROM json_array_elements(json_data) LOOP
        -- 获取当前对象的ID
        DECLARE
            current_id integer := (item->>'id')::integer;
        BEGIN
            -- 这里编写你需要根据ID执行的自定义逻辑，比如打印日志
            RAISE NOTICE '当前处理的ID: %', current_id;
            
            -- 返回当前对象（若需返回ID，可改为 RETURN NEXT current_id::json）
            RETURN NEXT item;
        END;
    END LOOP;
    
    RETURN;
END;
$$ LANGUAGE plpgsql;

调用示例

• 传入单个对象：

SELECT parse_json('{"id": 1,"name": "Amit","Class": "12th"}');

执行后会输出日志：当前处理的ID: 1，并返回该JSON对象。

• 传入对象数组：

SELECT parse_json('[{"id":1,"name":"Amit"},{"id":2,"name":"Bob"},{"id":3,"name":"Charlie"}]');

执行后会依次输出三个ID的日志，并返回三个JSON对象。

方案2：传入包含ID数组的JSON对象（如{"ids": [1,2,3], ...}） #

如果你的输入格式是包含ID数组的对象，可使用以下函数：

CREATE OR REPLACE FUNCTION parse_json_with_id_array(arg_t text) 
RETURNS setof integer
AS $$
DECLARE
    json_data json;
    id_array json;
    current_id integer;
BEGIN
    json_data := arg_t::json;
    -- 提取JSON中的ID数组
    id_array := json_data->'ids';
    
    -- 遍历ID数组
    FOR current_id IN SELECT * FROM json_array_elements_text(id_array)::integer LOOP
        -- 执行对应自定义逻辑
        RAISE NOTICE '当前处理的ID: %', current_id;
        
        -- 返回当前ID
        RETURN NEXT current_id;
    END LOOP;
    
    RETURN;
END;
$$ LANGUAGE plpgsql;

调用示例

SELECT parse_json_with_id_array('{"ids": [1,2,3], "type": "student"}');

执行后输出三个ID的日志，并返回这三个ID值。

内容的提问来源于stack exchange，提问作者Gaurav Gade