Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- unmodified setup
CREATE TABLE "order" (  -- order is reserved word
    id BIGSERIAL NOT NULL,
    platform TEXT NOT NULL,
    platform_order_id TEXT NOT NULL,
    primary key (id)
);
ALTER TABLE "order" ADD CONSTRAINT platform_order_id_unique UNIQUE (platform, platform_order_id);

CREATE TABLE order_item (
    id BIGSERIAL NOT NULL,
    order_id int8 NOT NULL,
    platform_item_id text NOT NULL,
    quantity INTEGER,
    primary key (id)
);
ALTER TABLE order_item ADD CONSTRAINT FK_order_item_order_id FOREIGN KEY (order_id) references "order";
ALTER TABLE order_item ADD CONSTRAINT platform_item_id_unique UNIQUE (order_id, platform_item_id);

CREATE TABLE return (
    id BIGSERIAL NOT NULL,
    order_id int8 NOT NULL,
    primary key (id)
);
ALTER TABLE return ADD CONSTRAINT FK_return_order_id FOREIGN KEY (order_id) references "order";

CREATE TABLE return_item (
    return_id int8 NOT NULL,
    order_item_id int8 NOT NULL,
    quantity integer NOT NULL
);
ALTER TABLE return_item ADD CONSTRAINT FK_return_item_return_id FOREIGN KEY (return_id) references return;
ALTER TABLE return_item ADD CONSTRAINT FK_return_item_item_id FOREIGN KEY (order_item_id) references order_item;

CREATE TABLE



ALTER TABLE



CREATE TABLE



ALTER TABLE



ALTER TABLE



CREATE TABLE



ALTER TABLE



CREATE TABLE



ALTER TABLE



ALTER TABLE


-- minimal example values
WITH ins_order AS (
   INSERT INTO "order"(platform, platform_order_id)
   VALUES ('foo', 'foo1')
   RETURNING id
   )
 , ins_order_item AS (
   INSERT INTO order_item(order_id, platform_item_id, quantity)
   SELECT id, 'foo', 5  -- buy 5
   FROM  ins_order
   RETURNING id
   )
 , ins_return AS (
   INSERT INTO return(order_id)
   SELECT id
   FROM  ins_order
   RETURNING id
   )
INSERT INTO return_item(return_id, order_item_id, quantity)
SELECT r.id, oi.id, 2  -- return 2
FROM   ins_return r, ins_order_item oi;

INSERT 0 1


TABLE "order";
TABLE order_item;
TABLE return;
TABLE return_item;


id
platform
platform_order_id


1
foo
foo1



SELECT 1




id
order_id
platform_item_id
quantity


1
1
foo
5



SELECT 1




id
order_id


1
1



SELECT 1




return_id
order_item_id
quantity


1
1
2



SELECT 1


-- trigger function
CREATE FUNCTION trg_return_item_insup_bef()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   _ordered_items int;
   _remaining_items int;
BEGIN
   SELECT quantity
   FROM   order_item
   WHERE  id = NEW.order_item_id
   FOR    NO KEY UPDATE                -- lock the parent row first ... (!)
   INTO   _ordered_items;              --  ... while fetching quantity

   SELECT _ordered_items - COALESCE(sum(quantity), 0)   -- COALESCE redundant with proper data model
   FROM   return_item
   WHERE  order_item_id = NEW.order_item_id
   INTO   _remaining_items;

   IF NEW.quantity > _remaining_items THEN
      RAISE EXCEPTION 'Tried to return % items, but only % of % are left.', NEW.quantity, _remaining_items, _ordered_items;
   END IF;
   
   RETURN NEW;
END
$func$;

CREATE FUNCTION


-- trigger
CREATE TRIGGER insup_bef
BEFORE INSERT OR UPDATE ON return_item
FOR EACH ROW
EXECUTE PROCEDURE trg_return_item_insup_bef();

CREATE TRIGGER


-- Returning too many
INSERT INTO return_item(return_id, order_item_id, quantity)
VALUES (1,1,4);

ERROR:  Tried to return 4 items, but only 3 of 5 are left.
CONTEXT:  PL/pgSQL function trg_return_item_insup_bef() line 18 at RAISE


-- Returning ok
INSERT INTO return_item(return_id, order_item_id, quantity)
VALUES (1,1,3);

INSERT 0 1


-- none left
INSERT INTO return_item(return_id, order_item_id, quantity)
VALUES (1,1,1);

ERROR:  Tried to return 1 items, but only 0 of 5 are left.
CONTEXT:  PL/pgSQL function trg_return_item_insup_bef() line 18 at RAISE