CREATE TABLE user_sequences (
    user_id uuid PRIMARY KEY,
    next_order_id bigint DEFAULT 1
);

INSERT INTO user_sequences (user_id) SELECT DISTINCT user_id FROM orders;

CREATE OR REPLACE FUNCTION next_order_id(user_id uuid) RETURNS bigint AS $$
DECLARE
    new_order_id bigint;
BEGIN
    UPDATE user_sequences
    SET next_order_id = next_order_id + 1
    WHERE user_id = next_order_id.user_id
    RETURNING next_order_id INTO new_order_id;

    RETURN new_order_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION set_order_id()
RETURNS TRIGGER AS $$
BEGIN
    NEW.order_id = next_order_id(NEW.user_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_order_id_trigger
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION set_order_id();