How to create multiple temp tables using records from a CTE that I need to call multiple times in Postgres plpgsql Procedure?

Question

I am already using a CTE expression within a plpgsql Procedure to grab some Foreign Keys from (1) specific table, we can call it master_table. I created a brand new table, we can call this table table_with_fks, in my DDL statements so this table holds the FKs I am fetching and saving.

I later take these FKs from my table_with_fks and JOIN on my other tables in my database to get the entire original record (the full record with all columns from its corresponding table) and insert it into an archive table.

I have an awesome lucid chart I drew that might make what I say down below make much more sense:

My CTE example:

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;

Now, I have (4) other Procedures that JOIN on each FK in this table_with_fks with its parent table that it references. I do this because as I said, I only got the FK at first, and I don't have all the original columns for the record. So I will do something like

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.

Is there a way to use the original CTE fk_list_cte and call it multiple times in succession and CREATE 4 TEMP tables right after, that all use the original CTE? example:

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;

I know the 4 CREATE TEMPORARY TABLE statements definitely won't work, (can I create 4 temp tables simultaneously/at once?) . Does anyone see the logic of what I am trying to do here and can help me?

Answer 1

You can't execute all of the CREATE TEMPORARY TABLEs in one statement. If you have a lot of these and want to avoid the copypasta you can write Dynamic SQL to build the queries and execute them one by one. Another option is to create views out of the CTEs and use those as the source for your temp tables. You could use materialized views too, though those are are persistent.

After running one of the CREATEs, master_table should be loaded in memory so later joins can just pull it out of cache, assuming you have plenty of memory to work with.