Блог

ОБНОВЛЕНИЕ: Я использую CTE, потому что я использую ЦИКЛ для цикла партиями по 10000.

Я уже использую выражение CTE в процедуре plpgsql для захвата некоторых внешних ключей из (1) определенной таблицы, мы можем ее вызвать master_table . Я создал совершенно новую таблицу, которую мы можем назвать этой таблицей table_with_fks , в моих операторах DDL, поэтому в этой таблице содержатся FKS, которые я извлекаю и сохраняю.

Позже я беру эти FK из своей table_with_fks и ОБЪЕДИНЯЮ с другими таблицами в своей базе данных, чтобы получить всю исходную запись (полную запись со всеми столбцами из соответствующей таблицы) и вставить ее в архивную таблицу.

У меня есть потрясающая ясная диаграмма, которую я нарисовал, которая может придать тому, что я говорю ниже, гораздо больше смысла:

Мой пример 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;
 

Теперь у меня есть (4) другие процедуры, которые JOIN на каждом FK в этом 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;
 

NOW, what I want to do, is instead of having to RE-JOIN 4 times later on these FK’s that are found in my table_with_fks , I want to use the first CTE fk_list_cte to JOIN on the parent tables right away and grab the full record from each (4) tables and put it in some TEMP postgres table. I think I will need (4) unique TEMP tables, as I don’t know how it would work if I combine all their data into one BIG table, because each table has different data/different columns.

Есть ли способ использовать исходный CTE fk_list_cte и вызывать его несколько раз подряд, а сразу после этого создать 4 временные таблицы, все из которых используют исходный 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 временные таблицы одновременно/одновременно?) . Кто-нибудь видит логику того, что я пытаюсь здесь сделать, и может мне помочь?