可以在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

- Alan Wayne

Answer 1

首先，通常情况下，如果您有重要的订单依赖关系，最好通过其他方式解决。 SQL 是一种声明性语言，没有排序的概念，因此我们在这里所做的任何事情都取决于实现细节，而不是标准预期行为。 您最好将其包装在用户定义的函数中，并更清晰地分解逻辑。 作为替代方案，您可以标记外键约束DEFERRABLE，并在运行此查询之前将其设置为DEFERRED（然后在查询之后将其设置为IMMEDIATE）。 这些将是最佳选择和解决问题的正确方法。

接下来是您特定的期望解决方案。 您在这里遇到的问题并不是通常需要对CTE进行排序，而更普遍地需要对连接操作进行排序。 我认为以下内容在这种特定情况下可能是安全的，但我并不完全确定（即更聪明的规划器可能会在未来破坏它）。

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
    left join planC C on C.cpt_recid = A.cpt_recid   -- there may not be a cpt_invoice for the cpt_recid.
    join planB B on B.cpt_recid = A.cpt_recid OR B.cpt_recid = c.cpt_recid
    order by b.cdesc
    returning cpt_recid, uid
)

我不完全相信这是长期解决方案的原因在于，更聪明的假设计划者可能会注意到c.cpt_recid始终等于a.cpt_recid，因此OR子句始终是多余的。

- Chris Travers

---

哇！即使经过这么长时间，我还是完全错过了“声明式”语言的概念。这真的让CTE的概念更加清晰明了。谢谢。 - Alan Wayne

Answer 2

最简单的解决方案是使计划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;

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

- Patrick

---

太棒了！我没有想到使用plpgsql - 这样更好！谢谢。 - Alan Wayne

只是一句旁注。我正在使用上述CTE来迁移整个表，而不仅仅是一条记录。 - Alan Wayne