您如何使用 CTE 編寫有序的多插入？

像這樣的表：

CREATE TABLE test_1 (
 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE test_2 (
 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE test_refs (
 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 id_1 bigint NOT NULL REFERENCES test_1,
 id_2 bigint NOT NULL REFERENCES test_2
);

和這樣的插入查詢：

WITH new_test_1_rows AS (
 INSERT INTO test_1
 DEFAULT VALUES
 RETURNING *
), new_test_2_rows AS (
 INSERT INTO test_2
 DEFAULT VALUES
 RETURNING *
), test_row_pairs AS (
 INSERT INTO test_refs
  ( id_1, id_2 )
 VALUES
   (
     (SELECT id FROM new_test_1_rows),
     (SELECT id FROM new_test_1_rows)
   )
 RETURNING *
)
SELECT *
FROM test_row_pairs

基本上它的作用：

• 插入一行test_1
• 插入一行test_2
• 將他們的 ID 對插入test_refs

問題是我想將查詢重寫為多插入查詢，即插入的行將行插入n，然後為插入的值創建行。為此，我需要知道 CTE 中的行索引，因此它可以用作連接鍵。你可以在條款中做些什麼嗎？test_1``n``test_2``n``test_refs``RETURNING

…對於n插入的行，test_1將 n 行插入test_2 ，然後為插入的值創建n行。test_refs為此，我需要知道 CTE 中的行索引，因此它可以用作連接鍵。RETURNING 你可以在條款中做些什麼嗎？

沒有在子句中添加行號的規定RETURNING- 沒有等效WITH ORDINALITY項可以與 set-returning 函式一起使用。看：

• 帶有元素編號的 PostgreSQL unnest()

如果你添加一個視窗函式（就像你可能已經嘗試過的那樣），你會得到：

ERROR:  window functions are not allowed in RETURNING

Postgres 特定的ROWS FROM語法可以在鎖步中返回行，但這也僅適用於返回集合的函式。看：

• 並行取消嵌套多個數組

您必須走很長一段路，並在另一個 CTE 或子查詢中生成行號。兩行範例：

WITH new_test_1_rows AS (
  INSERT INTO test_1 VALUES (DEFAULT), (DEFAULT)
  RETURNING *
  )
, new_test_2_rows AS (
  INSERT INTO test_2 VALUES (DEFAULT), (DEFAULT)
  RETURNING *
  )
INSERT INTO test_refs (id_1, id_2)
SELECT t1.id, t2.id
FROM  (
  SELECT row_number() OVER (ORDER BY id) AS rn, id  -- !
  FROM new_test_1_rows
  ) t1
JOIN  (
  SELECT row_number() OVER (ORDER BY id) AS rn, id  -- !
  FROM new_test_2_rows
  ) t2 USING (rn)  -- !
RETURNING *

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/315774