postgresql - 如何使用我需要在 Postgres plpgsql 过程中多次调用的 CTE 记录创建多个临时表？

Question

更新： 我正在使用 CTE，因为我使用 LOOP 以 10000 个批次循环。

我已经在 plpgsql 过程中使用 CTE 表达式从（1）特定表中获取一些外键，我们可以调用它master_table。我创建了一个全新的表，我们可以table_with_fks在我的 DDL 语句中调用这个表，所以这个表包含我正在获取和保存的 FK。

我稍后从我table_with_fks的数据库中的其他表中获取这些 FK 和 JOIN，以获取整个原始记录（包含相应表中所有列的完整记录）并将其插入到存档表中。

我画了一张很棒的清晰图表，这可能会使我在下面所说的更有意义：

我的 CTE 示例：

LOOP
   EXIT WHEN some_condition;

WITH fk_list_cte AS (
  SELECT mt.fk1, mt.fk2, mt.fk3, mt.fk4
  FROM master_table mt
  WHERE mt.created_date < now() - interval '365' // archive record if >= 1 year old
  LIMIT 10000
)
INSERT INTO table_with_fks (SELECT * FROM fk_list_cte);
commit;

END LOOP;

JOIN现在，我在其中的每个 FK 上都有 (4) 个其他过程，以及它引用table_with_fks的父表。我这样做是因为正如我所说，我一开始只得到了 FK，而且我没有记录所有原始列。所以我会做类似的事情

LOOP
   EXIT WHEN some_condition;

WITH full_record_cte AS (
  SELECT * 
  FROM table_with_fks fks
  JOIN parent_table1 pt1
  ON fks.fk1 = pt1.id
  LIMIT 10000),
  INSERT INTO (select * from full_record_cte);
  commit;

END LOOP;

现在，我想要做的是table_with_fks，我不想在我的这些 FK 上重新加入 4 次，而是想立即使用第一个 CTEfk_list_cte加入父表并从每个表中获取完整记录(4) 表并把它放在一些 TEMP postgres 表中。我想我需要 (4) 个唯一的 TEMP 表，因为我不知道如果我将它们的所有数据合并到一个 BIG 表中它会如何工作，因为每个表都有不同的数据/不同的列。

有没有办法使用原始 CTEfk_list_cte并连续多次调用它，然后立即创建 4 个 TEMP 表，所有这些都使用原始 CTE？例子：

LOOP
   EXIT WHEN some_condition;

WITH fk_list_cte AS (
  SELECT mt.fk1, mt.fk2, mt.fk3, mt.fk4
  FROM master_table mt
  WHERE mt.created_date < now() - interval '365' // archive record if >= 1 year old
  LIMIT 10000
),
WITH fetch_fk1_original_record_from_parent AS (
  SELECT * 
  FROM fk_list_cte cte
  JOIN parent_table1 pt1
  ON  cte.fk1 = pt1.id
),
WITH fetch_fk2_original_record_from_parent AS (
  SELECT * 
  FROM fk_list_cte cte
  JOIN parent_table2 pt2
  ON  cte.fk2 = pt2.id
),
WITH fetch_fk3_original_record_from_parent AS (
  SELECT * 
  FROM fk_list_cte cte
  JOIN parent_table3 pt3
  ON  cte.fk3 = pt3.id
),
WITH fetch_fk4_original_record_from_parent AS (
  SELECT * 
  FROM fk_list_cte cte
  JOIN parent_table4 pt4
  ON  cte.fk4 = pt4.id
),
CREATE TEMPORARY TABLE fk1_tmp_tbl AS (
  SELECT * 
  FROM fetch_fk1_original_record_from_parent
)
CREATE TEMPORARY TABLE fk2_tmp_tbl AS (
  SELECT * 
  FROM fetch_fk2_original_record_from_parent
)
CREATE TEMPORARY TABLE fk3_tmp_tbl AS (
  SELECT * 
  FROM fetch_fk3_original_record_from_parent
)
CREATE TEMPORARY TABLE fk4_tmp_tbl AS (
  SELECT * 
  FROM fetch_fk4_original_record_from_parent
);

END LOOP;

我知道这 4 个CREATE TEMPORARY TABLE语句肯定行不通，（我可以同时/一次创建 4 个临时表吗？）。有谁看到我在这里尝试做的事情的逻辑并且可以帮助我？

标签： postgresqlstored-proceduresplpgsqlcommon-table-expressionpostgresql-9.4