如何在删除后有条件地更新/插入找不到行？ [英] How to conditionally UPDATE/INSERT after DELETE that doesn't find rows?

问题描述

我正在尝试在删除不同表中的值后更新表。

这是我对此问题的简化函数查询：

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

推荐答案

如果DELETE未找到符合条件的行，则其RETURNING子句将返回无行。

标题要求有条件地更新/插入删除后的，但如果没有要删除的行，则正文会报告&q；失败。如果条件不是存在要删除的行，则条件是什么？

冒险，这个可能就是您想要的：

CREATE FUNCTION updateoutfit(_id UUID, _title text DEFAULT NULL::text, _garments json)
  RETURNS TABLE (id UUID, title text, garments json)
  LANGUAGE sql AS
$func$
DELETE FROM outfit_garment WHERE outfit_id = _id;  -- DELETE if exists

INSERT INTO outfit (id, title)  -- UPSERT outfit
VALUES (_id, _title)
ON CONFLICT (id) DO UPDATE
SET    title = EXCLUDED.title;
   
WITH ins AS (  -- INSERT new rows in outfit_garment
   INSERT INTO outfit_garment (position_x, outfit_id)
   SELECT "positionX", _id
   FROM   json_to_recordset(_garments) AS x("positionX" float)  -- outfit_id UUID was unused!
   RETURNING json_build_object('positionX', position_x) AS garments
   )
SELECT _id, _title, json_agg(garments)
FROM   ins
GROUP  BY id, title;
$func$;

它删除表outfit_garment中给定UUID的所有行，然后在表outfit中插入或更新行，最后在表outfit_garment中添加新的详细信息行。将忽略传入的任何outfit_id。
然后，它返回一行，所有服装都合并到一个JSON值中。

这篇关于如何在删除后有条件地更新/插入找不到行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！