更新时间:2023-02-14 19:09:13
如果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
。