database - 可以在 CTE 中订购 JOIN 操作吗？ (PostgreSQL)

Question

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

Answer 1

最简单的解决方案是使计划 B 依赖于计划 C(删除计划 A):

WITH planC as (
    delete from cpt_invoice D
    USING doctorbilling A
    where D.recid = A.cpt_recid
    returning D.recid, D.cpt_recid, D.ninsurance, D.ncash, D.mustschedule, D.doneinoffice
),
planB as (
    delete from cpt C
    USING planC X
    where C.recid = X.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, b.cpt_recid
    from planB B
    left join planC C on C.cpt_recid = B.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;

不过，这一切看起来有点奇怪，因为您正在对表 doctorbilling 中的所有行执行大量不合格的数据修改语句。实际上，您更有可能一次移动单个 cpt_recid，这会使查询更加直接:

WITH planC as (
    delete from cpt_invoice D
    where D.recid = <<cpt_recid>>
    returning D.recid, D.cpt_recid, D.ninsurance, D.ncash, D.mustschedule, D.doneinoffice
),
planB as (
    delete from cpt C
    USING planC X
    where C.recid = X.recid -- maintain dependency
    returning 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, b.cpt_recid
    from planB B
    left join planC C on true   -- there may not be a cpt_invoice for the cpt_recid.
    order by b.cdesc
    returning uid
)
update doctorbilling T
set uid = D.uid
from planD D
where T.cpt_recid = <<cpt_recid>>;

更好的是 PL/pgSQL 函数:

CREATE FUNCTION move_recid (id integer) RETURNS void AS $$
DECLARE
    ... -- declare all variables
BEGIN
    delete from cpt_invoice
    where recid = id
    returning cpt_recid, ninsurance, ncash, mustschedule, doneinoffice
         into inv_recid, inv_ins, inv_cash, inv_sch, inv_doi;

    delete from cpt
    where recid = id
    returning code, cdesc, procedure_type, sex
         into cpt_code, cpt_desc, cpt_proc, cpt_sex;

    insert into doctor_procedures (code, cdesc, procedure_type, sex, ninsurance, ncash,
                                   mustschedule, doneinoffice, cpt_recid)
    values (cpt_code, cpt_desc, ...)
    returning uid into dp_uid;

    update doctorbilling
    set uid = dp_uid
    where cpt_recid = id;
END;
$$ LANGUAGE plpgsql STRICT;

保证订单。易于其他程序员理解，易于维护。