Estoy tratando de actualizar la tabla después de eliminar el valor en una tabla diferente.
Esta es mi consulta de función simplificada para esta pregunta:
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; $$;Funciona bien si se devuelve outfit_id de eliminar:
DELETE FROM outfit_garment WHERE outfit_garment.outfit_id = _id RETURNING outfit_idpero falla si no hay una fila para eliminar. Intenté algo como esto:
DELETE FROM outfit_garment WHERE outfit_garment.outfit_id = '1234' RETURNING (SELECT '1234' as outfit_id );pero todavía devuelve 0 filas.
¿Hay alguna manera de arreglar eso o una mejor manera de hacerlo?
Estoy usando Postgres 13.2
Si DELETE no encuentra ninguna fila calificada, su cláusula RETURNING no devuelve filas .
El título solicita "ACTUALIZAR/INSERTAR condicionalmente después de ELIMINAR" , pero el cuerpo se queja de que "falla si no hay una fila para eliminar" . Si la existencia de una fila para eliminar no es la condición, ¿cuál es la condición?
Saliendo en una extremidad, esto podría ser lo que quieres:
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$; Elimina todas las filas de la tabla outfit_garment para el UUID dado, luego inserta o actualiza una fila en la tabla outfit y finalmente agrega nuevas filas de detalles en la tabla outfit_garment . Cualquier outfit_id pasado en _garments se ignora.
Luego devuelve una sola fila con todas las prendas fusionadas en un valor JSON.
Gracias por su consulta, @Erwin Brandstetter, resolví mi problema.
Esta es mi consulta final:
create function updateoutfit(_id uuid, _garments json, _title text DEFAULT NULL::text) returns TABLE ( id uuid, title text, garments json ) language sql as $$ DELETE FROM outfit_garment WHERE outfit_id = _id; WITH upd as ( UPDATE outfit SET title = _title WHERE outfit.id = _id RETURNING id, title ), ins AS ( insert into outfit_garment (position_x, garment_id, outfit_id) SELECT "positionX", "id", _id from json_to_recordset(_garments) as x("positionX" float, "id" uuid ) RETURNING json_build_object('positionX', position_x, 'garmentId', garment_id ) as garments ) SELECT id, title, json_agg(garments) from upd, ins group by id, title; $$;