My query is trying to do the following:

1. Add customer if doesn't exist
2. Add order if doesn't exist (if exists cancel req)
3. Add product(s) if doesn't exist
4. Add order_lines using both product and order as ref

Here is my current query:

WITH _customer AS (
  INSERT INTO customers (name, email)
  VALUES ('Mark', '[email protected]')
  ON CONFLICT ON CONSTRAINT customer_email_key
  DO
    UPDATE SET name = customers.name
  RETURNING id as customer_id
), _order AS (
  INSERT INTO orders (source_id, customer_id, posted_date, amount)
  SELECT '112', customer_id, '2021-10-31', 29.98 FROM _customer
  ON CONFLICT ON CONSTRAINT order_id_key DO NOTHING
  RETURNING id as order_id
), _product0 AS (
  INSERT INTO products (item, price)
  VALUES ('item1', 13.99)
  ON CONFLICT ON CONSTRAINT product_item_key
  DO
    UPDATE SET price = products.price
  RETURNING id as product_id
), _product1 AS (
  INSERT INTO products (item, price)
  VALUES ('item2', 15.99)
  ON CONFLICT ON CONSTRAINT product_item_key
  DO
    UPDATE SET price = products.price
  RETURNING id as product_id
)

INSERT INTO order_lines (product_id, order_id, amount, quantity)
SELECT product_id, order_id, 13.99, 1 FROM _product0, _order

INSERT INTO order_lines (product_id, order_id, amount, quantity)
SELECT product_id, order_id, 15.99, 1 FROM _product1, _order

Everything in this query works, unless I have more than 1 INSERT INTO order_lines

How do I do 2 separate inserts using the same WITH AS chain (since I need not only product ref, but also order ref) ?

CodePudding user response：

You can use:

INSERT INTO order_lines (product_id, order_id, amount, quantity)
SELECT product_id, order_id, 13.99, 1 FROM _product0, _order
union all 
SELECT product_id, order_id, 15.99, 1 FROM _product1, _order

instead of this:

INSERT INTO order_lines (product_id, order_id, amount, quantity)
SELECT product_id, order_id, 13.99, 1 FROM _product0, _order
INSERT INTO order_lines (product_id, order_id, amount, quantity)
SELECT product_id, order_id, 15.99, 1 FROM _product1, _order