sql – 将数据修改CTE中的INSERT语句与CASE表达式相结合

我的旧查询如下所示：

WITH x AS (
    INSERT INTO d (dm_id)
    SELECT dm_id
    FROM dm,import i
    WHERE  dm.dm_name = i.dm_name
    RETURNING d_id
),y AS (
    INSERT INTO z (d_id)
    SELECT d_id
    FROM x
    RETURNING z_id
)
INSERT INTO port (z_id)
SELECT z_id
FROM y;

这就像一个魅力.但是现在,添加了另一个表(r)(与表d相同的结构),并且有可能必须将d_id或r_id添加到表z.这取决于表导入中dm_name或rm_name是否为空.所以我的理论方法是这样的：

SELECT dm_name,rm_name,CASE WHEN dm_name != '' THEN 
        WITH x AS (
            INSERT INTO d (dm_id)
            SELECT dm_id
            FROM dm,import i
            WHERE  dm.dm_name = i.dm_name
            RETURNING d_id
        ),y AS (
            INSERT INTO z (d_id)
            SELECT d_id
            FROM x
            RETURNING z_id
        )
        INSERT INTO port (z_id)
        SELECT z_id
        FROM y
    END,CASE WHEN rm_name != '' THEN 
        WITH x AS (
            INSERT INTO r (rm_id)
            SELECT rm_id
            FROM rm,import i
            WHERE  rm.rm_name = i.rm_name
            RETURNING r_id
        ),y AS (
            INSERT INTO z (r_id)
            SELECT r_id
            FROM x
            RETURNING z_id
        )
        INSERT INTO port (z_id)
        SELECT z_id
        FROM y
    END

FROM import;

但是PostgreSQL告诉我：

syntax error at or near “INSERT INTO port (z_id)”

虽然查询的那部分应该是正确的,因为它已经有效.
我希望你能帮我解决这个问题.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!