PostgreSQL存储过程中复用JSON参数及用json_to_recordset更新表的方法

问题1 两种方案的选型说明 #

• 两种方案都可落地，按需选择即可：
  • 如果你的JSON入参是多条记录集合，优先用临时表存储解析结果：临时表可在整个存储过程生命周期内被多次查询、关联，完全满足复用需求，写法示例如下：

    -- 存储过程开头先解析JSON存入临时表
    CREATE TEMP TABLE IF NOT EXISTS temp_parsed_json AS
    SELECT 
      json_data1,
      json_data2
    FROM json_to_recordset(json_parameter) x
    (
        json_data1 int,
        json_data2 boolean
    );
    

    后续INSERT、UPDATE语句直接查询temp_parsed_json表即可，存储过程执行结束后临时表会自动清理，无需手动维护。
  • 如果你的JSON入参固定只有单条记录，可以用SELECT INTO给变量赋值，比临时表更轻量化：

    -- 单条数据场景赋值给变量
    SELECT json_data1, json_data2 
    INTO v_data1, v_data2
    FROM json_to_recordset(json_parameter) x
    (
        json_data1 int,
        json_data2 boolean
    )
    LIMIT 1;
    

问题2 基于json_to_recordset的UPDATE示例 #

以下示例直接关联解析后的JSON结果集更新业务表，同时包含now()::timestamp(0)等非JSON来源的字段赋值：

UPDATE myBusinessTable t
SET 
  -- 非JSON来源的更新时间字段
  update_time = now()::timestamp(0),
  -- 非JSON来源的操作人字段
  operator = current_user,
  -- JSON来源的业务字段
  business_status = x.json_data2,
  remark = '批量更新自JSON入参'
FROM json_to_recordset(json_parameter) x
(
    json_data1 int,
    json_data2 boolean
)
-- 业务表和JSON解析结果的关联条件，可根据实际业务调整
WHERE t.business_id = x.json_data1;

如果已经用了临时表存储解析结果，把FROM后的json_to_recordset(...)替换成临时表名即可，逻辑完全一致。

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