时间戳

首页 > 解决方案 > 删除 CTE 后 COALESCE 失败。(PostgreSQL)

postgresql - 删除 CTE 后 COALESCE 失败。(PostgreSQL)

问题描述

PostgreSQL 11.1 PgAdmin 4.1

这在某些时候有效：

BEGIN;
    SET CONSTRAINTS ALL DEFERRED;

    WITH _in(trx, lastname, firstname, birthdate, old_disp, old_medname, old_sig, old_form, new_disp, new_medname, new_sig, new_form, new_refills)  AS (
            VALUES ('2001-06-07 00:00:00'::timestamp, 
                    UPPER(TRIM('JONES')), UPPER(TRIM('TOM')), '1952-12-30'::date,
                    64::integer, 
                    LOWER(TRIM('adipex 37.5mg tab')), LOWER(TRIM('one tab po qd')), LOWER(TRIM('tab')),
                    63::integer,
                    LOWER(TRIM('adipex 37.5mg tab')), LOWER(TRIM('one tab po qd')), LOWER(TRIM('tab')),
                    33::integer
                    )
            ),
        _s AS (                 -- RESOLVE ALL SURROGATE KEYS.
                SELECT n.*, d1.recid as old_medication_recid, d2.recid as new_medication_recid, pt.recid as patient_recid
                FROM _in n
                JOIN medications d1 ON (n.old_medname, n.old_sig, n.old_form) = (d1.medname, d1.sig, d1.form)
                JOIN medications d2 ON (n.new_medname, n.new_sig, n.new_form) = (d2.medname, d2.sig, d2.form)
                JOIN patients pt ON (pt.lastname, pt.firstname, pt.birthdate) = (n.lastname, n.firstname, n.birthdate)
        ),
         _t AS (               -- REMOVE CONFLICTING RECORD, IF ANY.
                DELETE FROM rx r
                USING _s n
                WHERE (r.trx::date, r.disp, r.patient_recid, r.medication_recid)=(n.trx::date, n.new_disp, n.patient_recid, n.new_medication_recid)
                RETURNING r.*
            ),
          _u  AS(               -- GET NEW SURROGATE KEY.
                SELECT COALESCE(_t.recid, r.recid) as target_recid, r.recid as old_recid
                FROM _s n
                JOIN rx r ON (r.trx, r.disp, r.patient_recid, r.medication_recid) = (n.trx, n.old_disp, n.patient_recid, n.old_medication_recid)
                LEFT JOIN _t ON (_t.trx::date, _t.disp, _t.patient_recid, _t.medication_recid) = (n.trx::date, n.new_disp, n.patient_recid, n.new_medication_recid)
            )                                   
        UPDATE rx r           -- UPDATE ORIGINAL RECORD WITH NEW VALUES.
        SET disp = n.new_disp, medication_recid = n.new_medication_recid, refills = n.new_refills, recid = _u.target_recid
        FROM _s n, _u
        WHERE r.recid = _u.old_recid
        RETURNING r.*;

COMMIT;

其中表 rx 定义为：

CREATE TABLE phoenix.rx
(
    recid integer NOT NULL DEFAULT nextval('rx_recid_seq'::regclass),
    trx timestamp without time zone NOT NULL,
    disp integer NOT NULL,
    refills integer,
    tprinted timestamp without time zone,
    tstop timestamp without time zone,
    modified timestamp without time zone DEFAULT now(),
    patient_recid integer NOT NULL,
    medication_recid integer NOT NULL,
    dposted date NOT NULL,
    CONSTRAINT pk_rx_recid PRIMARY KEY (recid),
    CONSTRAINT rx_unique UNIQUE (dposted, disp, patient_recid, medication_recid)

        DEFERRABLE,
    CONSTRAINT rx_medication_fk FOREIGN KEY (medication_recid)
        REFERENCES phoenix.medications (recid) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT
        DEFERRABLE,
    CONSTRAINT rx_patients FOREIGN KEY (patient_recid)
        REFERENCES phoenix.patients (recid) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT
)

几个小时后，发现冲突记录的“删除..”按预期工作，但在决定 rx.recid 的新代理键（主键）时，“COALESCE”语句似乎失败了——确实如此似乎没有收到删除的结果。（或者可能时机不对？？？）

非常感激任何的帮助。

TIA

标签： postgresqlconstraintscommon-table-expressioncoalesce

解决方案

这是记录在案的：

中的子语句WITH彼此同时执行并与主查询同时执行。因此，当在 中使用数据修改语句时WITH，指定更新实际发生的顺序是不可预测的。所有语句都使用同一个快照执行（参见第 13 章，因此它们无法“看到”彼此对目标表的影响。

如果 CTE 出现在 DML 语句中，请勿在具有 CTE 的语句中使用同一个表两次。而是DELETE ... RETURNING在语句的其他部分使用和使用返回的值。

如果您不能像那样重写语句，请使用多个语句，而不是将所有内容放入单个 CTE。

推荐阅读