PostgreSQL 9.5
下面的公用表达式(CTE)可以正确地将来自CPT和CPT_INVOICE表的记录移动到DOCTOR_PROCEDURES表,并相应地更新DOCTORBILLING uid。然而,CPT_INVOICE有一个指向其父表CPT的外键,因此在删除该外键关系之前,此脚本会失败。
是否有任何方法可以强制PostgreSQL按特定顺序执行CTE,即先执行planC,再执行planB?
TIA
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