在限制存储数据的同时确保Upsert返回RETURNING结果

针对你的scoped_data表需求，这里提供一个符合要求的PL/pgSQL函数实现，既支持批量Upsert，又能自动过滤掉NULL或空JSON对象('{}')的数据：

CREATE OR REPLACE FUNCTION upsert_scoped_data(p_records jsonb)
RETURNS void AS $$
DECLARE
BEGIN
  WITH filtered_records AS (
    -- 先过滤掉data为NULL或空JSON对象的无效记录
    SELECT
      (rec ->> 'owner_id')::text AS owner_id,
      (rec ->> 'scope')::text AS scope,
      (rec ->> 'key')::text AS key,
      (rec ->> 'data')::json AS data
    FROM jsonb_array_elements(p_records) rec
    WHERE
      (rec ->> 'data') IS NOT NULL
      AND (rec ->> 'data')::json != '{}'::json
  ), upserts AS (
    INSERT INTO scoped_data(owner_id, scope, key, data)
    SELECT owner_id, scope, key, data FROM filtered_records
    ON CONFLICT (owner_id, scope, key) DO UPDATE
      -- 仅当新数据有效时才执行更新，避免覆盖原有有效数据
      SET data = EXCLUDED.data
      WHERE
        EXCLUDED.data IS NOT NULL
        AND EXCLUDED.data != '{}'::json
  )
  SELECT 1; -- 触发CTE执行链
END;
$$ LANGUAGE plpgsql;

关键细节说明： #

• 前置过滤：通过filtered_records CTE先筛除无效数据，避免无效请求进入Upsert流程，减少不必要的数据库操作开销。
• 安全更新逻辑：在ON CONFLICT DO UPDATE中增加判断条件，确保只有有效的data才会覆盖原有记录——如果传入的更新数据是无效的，会直接保留原数据，不会出现“有效数据被改成无效”的情况。
• 批量处理支持：函数接收jsonb数组参数，可一次性处理多条不同scope的记录，完美适配你“单次事务插入多scope数据”的场景。

使用示例： #

你可以通过以下方式调用函数，批量插入/更新数据：

SELECT upsert_scoped_data('[
  {"owner_id": "user1", "scope": "profile", "key": "name", "data": {"value": "Alice"}},
  {"owner_id": "user1", "scope": "settings", "key": "notifications", "data": {}}, -- 此条会被过滤
  {"owner_id": "user2", "scope": "profile", "key": "age", "data": null}, -- 此条会被过滤
  {"owner_id": "user1", "scope": "profile", "key": "name", "data": {"value": "Alice Smith"}} -- 此条会更新现有记录
]'::jsonb);

额外优化建议： #

如果需要频繁判断data是否为无效值，可以创建一个自定义函数简化逻辑：

CREATE OR REPLACE FUNCTION is_empty_json(j json) RETURNS boolean AS $$
BEGIN
  RETURN j IS NULL OR j = '{}'::json;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

之后就能用WHERE NOT is_empty_json(data)替代原来的复合条件，让代码更简洁易读。

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