将数据修改CTE中的INSERT语句与CASE表达式相结合 [英] Combining INSERT statements in a data-modifying CTE with a CASE expression

问题描述

我的问题是在正确使用 WITH 的线程。

My question is some kind of extension to Erwin Brandstetter's excellent answer in this thread on the correct use of WITH.

我的旧查询如下：

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 在表 import 。所以我的理论方法是这样的：

This works like a charm. But now, another table (r) has been added (same structure as table d) and with it the possibility that either d_id or r_id has to be added to table z. This, depending on whether dm_name or rm_name is empty in table import. So my theoretical approach would be something like this:

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告诉我：

But PostgreSQL tells me:

$在 INSERT INTO端口（z_id）或附近的b $ b

语法错误

syntax error at or near "INSERT INTO port (z_id)"

尽管该部分查询应该

我希望您能帮助我解决此问题。 ：）

although that part of the query should be correct as it works already.
I hope you can help me solve this. :)

为了更好地理解-这是表格结构：

For a better understanding - here's the table structure:

CREATE TABLE import (
    dm_name character varying,
    rm_name character varying
    -- many other columns which are not relevant
);

CREATE TABLE dm (
    dm_id integer NOT NULL, -- serial
    dm_name character varying
    -- plus more columns
);

CREATE TABLE d (
    d_id integer NOT NULL, -- serial
    dm_id integer -- references dm.dm_id
    -- plus more columns
);

CREATE TABLE rm (
    rm_id integer NOT NULL, -- serial
    rm_name character varying
    -- plus more columns
);

CREATE TABLE r (
    r_id integer NOT NULL, -- serial
    rm_id integer -- references rm.rm_id
    -- plus more columns
);

CREATE TABLE z (
    z_id integer NOT NULL, -- serial
    r_id integer, -- references r.r_id
    d_id integer -- references d.d_id
    -- plus more columns
);

CREATE TABLE port (
    p_id integer NOT NULL, -- serial
    z_id integer, -- references z.z_id
    -- plus more columns
);

导入表不知道在雾化过程中生成的ID。 dm和rm表用于已从导入表中提取的设备型号。 d和r表适用于实际设备。由于端口只能具有r设备或d设备，而不能具有任何设备，因此引入z表时，端口表中只有一个字段代表所有可能性。 d / r和dm / rm表不能合并，因为它们具有不同的特殊列，具体取决于设备类型。

The import table doesn't know the ids as they are generated during the atomization process. The dm and rm tables are for device models which were already extracted from the import table. The d and r tables are for the actual devices. As a port only can only have either a r-device or a d-device or none, the z-table was introduced to have only one field in the port-table representing all possibilities. The d/r and dm/rm tables can't be combined as they have different special columns depending on the device types.

推荐答案

您不能在 CASE 表达式中嵌套 INSERT 语句。从我所见，这种完全不同的方法应该做到：

You cannot nest INSERT statements in a CASE expression. Deriving from what I see, this completely different approach should do it:

• 您实际上不需要外部的 SELECT 。

dm_name / rm_name 在 dm / 中定义为唯一rm 且不为空（<> c）。您应该具有 CHECK 约束来确保。

dm_name / rm_name are defined unique in dm / rm and not empty (<> ''). You should have a CHECK constraint to make sure.

两个 z 中的> d_id 和 r_id 为NULL（默认值）。

Column default for both d_id and r_id in z are NULL (default).

如果两者从未同时出现。

If both are never present at the same time.

WITH d1 AS (
   INSERT INTO d (dm_id)
   SELECT dm.dm_id 
   FROM   import
   JOIN   dm USING (dm_name)
   RETURNING d_id
   )
, r1 AS (
   INSERT INTO r (rm_id)
   SELECT rm.rm_id 
   FROM   import
   JOIN   rm USING (rm_name)
   RETURNING r_id
   )
, z1 AS (
   INSERT INTO z (d_id, r_id)
   SELECT d_id, r_id
   FROM d1 FULL JOIN r1 ON FALSE
   RETURNING z_id
   )
INSERT INTO port (z_id)
SELECT z_id
FROM   z1;

FULL JOIN ..FALSE 产生派生表，其中来自 d1 和 r1 的所有行均附加了NULL，而其他各列则没有（这两者之间没有重叠） ）。所以我们只需要一个 INSERT 而不是两个。较小的优化。

The FULL JOIN .. ON FALSE produces a derived table with all rows from d1 and r1 appended with NULL for the respective other column (no overlap between the two). So we just need one INSERT instead of two. Minor optimization.

WITH i AS (
   SELECT dm.dm_id, rm.rm_id
   FROM   import
   LEFT   JOIN dm USING (dm_name)
   LEFT   JOIN rm USING (rm_name)
   )
, d1 AS (
   INSERT INTO d (dm_id)
   SELECT dm_id FROM i WHERE dm_id IS NOT NULL
   RETURNING dm_id, d_id
   )
, r1 AS (
   INSERT INTO r (rm_id)
   SELECT rm_id FROM i WHERE rm_id IS NOT NULL
   RETURNING rm_id, r_id
   )
, z1 AS (
   INSERT INTO z (d_id, r_id)
   SELECT d1.d_id, r1.r_id
   FROM   i
   LEFT   JOIN d1 USING (dm_id)
   LEFT   JOIN r1 USING (rm_id)
   WHERE  d1.dm_id IS NOT NULL OR
          r1.rm_id IS NOT NULL
   RETURNING z_id
   )
INSERT INTO port (z_id)
SELECT z_id FROM z1;

注释

如果

Notes

Both versions also work if neither exists.

INSERT 如果 SELECT 不插入任何内容

如果必须处理可能与该操作冲突的并发写访问，则快速解决方案是在运行之前锁定相关表该语句在同一笔交易中。

If you have to deal with concurrent write access that could conflict with this operation the quick fix would be to lock involved tables before you run this statement in the same transaction.

这篇关于将数据修改CTE中的INSERT语句与CASE表达式相结合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！