Merging two 'tables' (function outputs) 1 to 1 Let's say I have two functions. We'll represent them as tables: order_items, order_details.

CREATE TABLE order_items
    ("order_item_id" int, "product_id" int, "quantity" int)
;

INSERT INTO order_items
    ("order_item_id", "product_id", "quantity")
VALUES
    (10, 1, 1),
    (11, 1, 1),
    (12, 1, 1),
    (13, 2, 2)
;

CREATE TABLE order_details
    ("order_details" jsonb, "product_id" int, "quantity" int)
;

INSERT INTO order_details
    ("order_details", "product_id", "quantity")
VALUES
    ('{"1":2,"2":5}'::jsonb, 1, 1),
    ('{"1":2,"2":6}'::jsonb, 1, 1),
    ('{"1":1,"2":4}'::jsonb, 1, 1),
    ('{"1":1,"2":4}'::jsonb, 2, 2)
;


I would like to take their outputs and have an output that looks like this:

("order_details", "product_id", "quantity", "order_item_id")
('{"1":2,"2":5}'::jsonb, 1, 1, 10),
('{"1":2,"2":6}'::jsonb, 1, 1, 11),
('{"1":1,"2":4}'::jsonb, 1, 1, 12),
('{"1":1,"2":4}'::jsonb, 2, 2, 13)


Basically, order_details can be associated with any order_item_id as long as the product_id and quantity match. The output must return 4 rows.

I've been trying to do this in plain SQL, but I am starting to feel like it's not possible. Using plpgsql is fine, but I figure there is a faster way than doing a double nested for-loop.
Just assign a unique index in both tables, then use that for the join:

SELECT order_details, product_id, quantity, order_item_id
FROM (SELECT order_item_id, product_id, quantity,
             row_number() OVER (PARTITION BY product_id, quantity) AS indx
      FROM order_items) AS items
JOIN (SELECT order_details, product_id, quantity,
             row_number() OVER (PARTITION BY product_id, quantity) AS indx
      FROM order_details) AS details
USING (product_id, quantity, indx);