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.
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?