SQL链式WITH AS语句中实现多order_lines INSERT的问题求助

需求说明 #

需要实现以下业务逻辑：

• 客户不存在则新增，存在则返回现有ID
• 订单不存在则新增，存在则终止整个请求（不插入任何数据）
• 多个产品不存在则新增，存在则返回现有ID
• 基于产品和订单关联关系，插入对应的订单行

原问题 #

原查询在执行单个INSERT INTO order_lines时正常，但同时执行多个时出现语法错误——PostgreSQL不允许一个CTE链后跟随多个独立的DML语句。

原SQL如下：

WITH _customer AS (
  INSERT INTO customers (name, email)
  VALUES ('Mark', 'test@test.com')
  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

解决方案 #

方案1：合并订单行插入（UNION ALL） #

将两个订单行的查询用UNION ALL合并，作为单个INSERT的数据源，满足PostgreSQL单个主语句的要求：

WITH _customer AS (
  INSERT INTO customers (name, email)
  VALUES ('Mark', 'test@test.com')
  ON CONFLICT ON CONSTRAINT customer_email_key
  DO UPDATE SET name = customers.name  -- 无实际更新，仅返回现有客户ID
  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
UNION ALL
SELECT product_id, order_id, 15.99, 1 FROM _product1, _order;

方案2：合并产品插入+批量关联 #

将多个产品的插入合并为单个CTE，再一次性关联订单插入所有订单行，代码更简洁：

WITH _customer AS (
  INSERT INTO customers (name, email)
  VALUES ('Mark', 'test@test.com')
  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
), _products AS (
  -- 合并多个产品的插入操作
  INSERT INTO products (item, price)
  VALUES ('item1', 13.99), ('item2', 15.99)
  ON CONFLICT ON CONSTRAINT product_item_key
  DO UPDATE SET price = products.price
  RETURNING id as product_id, price
)
-- 一次性插入所有订单行
INSERT INTO order_lines (product_id, order_id, amount, quantity)
SELECT 
  p.product_id, 
  o.order_id, 
  p.price, 
  1
FROM _products p
CROSS JOIN _order o;

关键说明 #

• PostgreSQL规定一个CTE链只能跟随一个主SQL语句，多个独立INSERT会触发语法错误，这是原查询失败的核心原因。
• 两种方案都能保证：如果订单已存在（_order返回空），则订单行不会插入任何数据，符合“订单存在则取消请求”的要求。
• 方案2更适合批量产品场景，减少冗余CTE代码；方案1保留了单个产品的独立CTE，适合产品属性差异较大的情况。

内容的提问来源于stack exchange，提问作者Michael