How to UPDATE after DELETE in postgreSQL

user_2919544 0
Viewed 261

I am trying to update the table after deleting value in different table.

This is my simplified function query for this question:

create function updateoutfit(_id uuid,  _title text DEFAULT NULL::text)
    returns TABLE(id uuid, title text)
    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 = del.outfit_id
         RETURNING id, title
     )
SELECT id,
       title
from updateOutfit as outfit
group by id, title;
$$;

It works well if there is outfit_id returned from delete:

DELETE FROM outfit_garment WHERE outfit_garment.outfit_id = _id RETURNING outfit_id

but fails if there is no row to delete. I tried something like this:

DELETE FROM outfit_garment WHERE outfit_garment.outfit_id = '1234' RETURNING (SELECT '1234' as outfit_id );

but it still returns 0 rows.

Is there a way to fix that or better way to do it?

I am using postgres 13.2

postgresql
1 Answers

Seems like your UPDATE should be unconditional. So separate the DELETE. Like:

CREATE FUNCTION updateoutfit(_id UUID, _title text DEFAULT NULL::text)
  RETURNS table(id UUID, title text)
  LANGUAGE sql AS
$func$
DELETE FROM outfit_garment
WHERE  outfit_garment.outfit_id = _id;

WITH upd AS (
   UPDATE outfit
   SET    title = _title
   FROM   del
   WHERE  outfit.id = _id
   RETURNING id, title
   )
SELECT DISTINCT id, title
FROM   upd;
$func$;
user_9398600
Related