PostgreSQL: Inserting tuples in multiple tables using a view and a trigger

I am trying to build an order system that is able to insert a compound order that consists of multiple items and amounts. My database layout is as follows: I have an order table, containing an autoincrement id, item_id, amount and order_group_id columns. I also have an order_group table containing an autoincrement id and a person_id column. The idea is that when a person orders, one new order_group entry is created, and its id is used as the fk in the orders that the person has done.

I presume that this would normally be done in the code of the application. However, I am using postgrest to provide an API for me, which suggests creating a custom view to insert compound entries via that route. This is described here.

This is what I have so far:

CREATE FUNCTION kzc.new_order()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
    DECLARE
        group_id int;
    BEGIN
        INSERT INTO kzc.order_group (person) VALUES (new.person) RETURNING id AS group_id;
        INSERT INTO kzc."order" (item, amount, order_group) VALUES (new.item_id, new.amount, group_id);
        RETURN new;
    END;
$$;

CREATE TRIGGER new_order
INSTEAD OF INSERT ON kzc.new_order
FOR EACH ROW
EXECUTE FUNCTION kzc.new_order()

However, this code makes a new ordergroup for every order that is in the compound insert. How can I make it so that my code only makes one new ordergroup entry and assigns its id to all orders?

Thanks in advance!

Answer

I suggest that you add an order_group_id column to the new_order view and create a sequence for it. Then create a DEFAULT value for the column:

ALTER VIEW kzc.new_order
   ALTER order_group_id SET DEFAULT currval('order_group_id_seq');

Add a BEFORE INSERT trigger FOR EACH STATEMENT that just calls nextval for the sequence. The currval calls will all pick up the same generated value.

Then you have that number in your trigger and can use it as a primary key for order_group.

To avoid adding the row multiple times, use

INSERT INTO kzc.order_group (id, person)
   VALUES (NEW.order_group_id, NEW.person)
   ON CONFLICT (id) DO NOTHING;