如何在DELETE未找到匹配行时执行条件化UPDATE/INSERT操作？

这个问题的核心是你的CTE依赖了DELETE语句的返回结果：当DELETE没有找到可删除的行时，del这个CTE会是空的，导致后续的updateOutfit因为FROM del的连接条件没有匹配行，根本不会执行更新操作，最终整个函数返回空或者报错。

方案一：确保del CTE始终返回目标ID #

你可以修改del CTE，让它不管DELETE是否命中行，都返回传入的_id。通过UNION ALL结合NOT EXISTS来实现：

CREATE FUNCTION updateoutfit(_id uuid, _title text DEFAULT NULL::text, _garments json)
RETURNS TABLE(id uuid, title text, garments json)
LANGUAGE sql AS $$
WITH del AS (
  -- 先执行删除，返回被删除行的outfit_id
  DELETE FROM outfit_garment WHERE outfit_id = _id RETURNING outfit_id
  UNION ALL
  -- 如果没有找到可删除的行，直接返回传入的_id
  SELECT _id AS outfit_id WHERE NOT EXISTS (SELECT 1 FROM outfit_garment WHERE outfit_id = _id)
),
updateOutfit AS (
  UPDATE outfit SET title = _title FROM del WHERE outfit.id = _id RETURNING id, title
),
saveOutfitGarment AS (
  INSERT INTO outfit_garment (position_x, outfit_id)
  SELECT "positionX", (SELECT id FROM updateOutfit)
  FROM json_to_recordset(_garments) AS x("positionX" float, outfit_id uuid)
  RETURNING json_build_object('positionX', position_x) AS garments
)
SELECT id, title, json_agg(garments) 
FROM updateOutfit AS outfit, saveOutfitGarment AS garments 
GROUP BY id, title;
$$;

这个方法的原理是强制del CTE始终有一行数据，这样updateOutfit的FROM del就能正常匹配，确保UPDATE操作执行。

方案二：简化逻辑，摆脱对DELETE返回结果的依赖 #

其实你的UPDATE操作根本不需要依赖DELETE的结果——你本来就是要更新指定_id的outfit行，不管有没有删除关联的outfit_garment。我们可以简化整个函数逻辑：

CREATE FUNCTION updateoutfit(_id uuid, _title text DEFAULT NULL::text, _garments json)
RETURNS TABLE(id uuid, title text, garments json)
LANGUAGE sql AS $$
WITH del AS (
  -- 执行删除操作，不管有没有匹配行都无需返回结果
  DELETE FROM outfit_garment WHERE outfit_id = _id
),
updateOutfit AS (
  -- 直接更新指定ID的outfit，无需关联del
  UPDATE outfit SET title = _title WHERE id = _id RETURNING id, title
),
saveOutfitGarment AS (
  -- 插入新的关联记录，直接使用传入的_id作为outfit_id
  INSERT INTO outfit_garment (position_x, outfit_id)
  SELECT "positionX", _id
  FROM json_to_recordset(_garments) AS x("positionX" float, outfit_id uuid)
  RETURNING json_build_object('positionX', position_x) AS garments
)
-- 使用LEFT JOIN处理无新garment的情况，用COALESCE确保返回空数组而非NULL
SELECT 
  outfit.id, 
  outfit.title, 
  COALESCE(json_agg(garments.garments), '[]'::json) AS garments
FROM updateOutfit AS outfit
LEFT JOIN saveOutfitGarment AS garments ON true
GROUP BY outfit.id, outfit.title;
$$;

这个方案的优势：

• 逻辑更清晰，每个CTE只负责单一职责
• 避免了依赖DELETE结果带来的潜在问题
• 用LEFT JOIN和COALESCE处理_garments为空的情况，确保返回的garments字段是标准的空JSON数组[]，而不是NULL

为什么你之前的尝试无效？ #

你尝试的DELETE ... RETURNING (SELECT '1234' as outfit_id )之所以失败，是因为PostgreSQL的RETURNING子句只会为被删除的每一行返回结果。如果DELETE没有匹配到任何行，整个RETURNING不会产生任何输出，不管你在子查询里写什么内容。

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