我试图在删除不同表中的值后更新表。
这是我对这个问题的简化函数查询:
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_garment.outfit_id = _id RETURNING outfit_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 updateOutfit.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;
$$;
如果删除返回了outfit_id,效果很好:
DELETE FROM outfit_garment WHERE outfit_garment.outfit_id = _id RETURNING outfit_id
但如果没有要删除的行,则会失败。我尝试过这样的事情:
DELETE FROM outfit_garment WHERE outfit_garment.outfit_id = '1234' RETURNING (SELECT '1234' as outfit_id );
但它仍然返回 0 行。
有没有办法解决这个问题或者更好的方法?
我正在使用 Postgres 13.2