如何修改PostgreSQL函数实现元组转JSON存入数组返回

修改后的可运行代码 #

版本1（保留你原有的手动字段构造逻辑）

CREATE OR REPLACE FUNCTION function_name (p_query_date DATE, p_query_name text)
RETURNS json language plpgsql AS $$
BEGIN 
    -- 下方JOIN关联条件请替换为你两张表实际的外键关联规则
    RETURN json_agg(row_to_json(row(p.name, p.age, a.address, a.locality, p.ID)))
    FROM people p
    JOIN addresses a ON p.address_id = a.id
    -- 下方WHERE条件补充你需要的日期、名称过滤逻辑
    WHERE p.name = p_query_name;
END;
$$;

版本2（生成带字段名的JSON，更易维护）

如果不需要无字段名的JSON数组，更推荐这个写法，生成的JSON对象会自动带上SELECT语句中的字段名：

CREATE OR REPLACE FUNCTION function_name (p_query_date DATE, p_query_name text)
RETURNS json language plpgsql AS $$
BEGIN 
    -- 查询无匹配数据时返回空数组而非null，可将下一行改为 RETURN COALESCE(json_agg(to_json(result_row)), '[]'::json)
    RETURN json_agg(to_json(result_row))
    FROM (
        SELECT p.name, p.age, a.address, a.locality, p.ID
        FROM people p
        JOIN addresses a ON p.address_id = a.id
        WHERE p.name = p_query_name
    ) result_row;
END;
$$;

核心修改说明 #

• 参数名加了p_前缀，避免和表中同名字段混淆，导致查询逻辑异常
• 移除了原代码的PERFORM关键字（该关键字作用是执行查询但丢弃返回结果），直接用RETURN返回json_agg的聚合结果，匹配函数声明的json返回值类型
• 把原代码隐式笛卡尔积连接改为显式JOIN，避免生成大量错误的关联数据
• 补充了查询过滤的位置，你可以根据业务需求把传入的日期、名称参数加到WHERE条件中

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