The below CTE works correctly to move records from tables CPT and CPT_INVOICE to DOCTOR_PROCEDURES and update DOCTORBILLING uid accordingly. However, CPT_INVOICE has a foreign key to its parent, CPT, so this script fails until that foreign key relationship is removed.
Is there any way of forcing PostgreSQL to execute CTE's in a specific order, i.e., to first execute planC before planB?
WITH planA AS ( select cpt_recid from doctorbilling ), planC as ( delete from cpt_invoice D USING planA a where D.recid = A.cpt_recid returning D.cpt_recid, D.ninsurance, D.ncash, D.mustschedule, D.doneinoffice ), planB as ( delete from cpt C USING planA A where C.recid = A.cpt_recid returning C.recid as cpt_recid, C.code, C.cdesc, C.procedure_type, C.sex ), planD as ( insert into doctor_procedures (code, cdesc, procedure_type, sex, ninsurance, ncash, mustschedule, doneinoffice, cpt_recid) select distinct on (b.cdesc) b.code, b.cdesc, b.procedure_type, b.sex, c.ninsurance, c.ncash, c.mustschedule, c.doneinoffice, a.cpt_recid from planA A join planB B on B.cpt_recid = A.cpt_recid left join planC C on C.cpt_recid = A.cpt_recid -- there may not be a cpt_invoice for the cpt_recid. order by b.cdesc returning cpt_recid, uid ) update doctorbilling T set uid = D.uid from planD D where T.cpt_recid = D.cpt_recid