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, _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;
$$;

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

CodePudding user response：

If the DELETE finds no qualifying row, its RETURNING clause returns no rows.

The title asks to "conditionally UPDATE/INSERT after DELETE", but the body complains it "fails if there is no row to delete". If the existence of a row to delete is not the condition, then what is the condition?

Going out on a limb, this might be what you want:

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$;

It deletes all rows from table outfit_garment for the given UUID, then either inserts or updates a row in table outfit, and finally adds new detail rows in table outfit_garment. Any outfit_id passed in _garments are ignored.
Then it returns a single row with all garments amalgamated into a JSON value.

CodePudding user response：

Thanks for your query @Erwin Brandstetter I resolved my issue.

This is my final query:

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;
$$;