CODEWITHSUNDEEP
sqlpostgresqlcasecommon-table-expressionsql-insert
Picl
Picl

Reputation: 149

Combining INSERT statements in a data-modifying CTE with a CASE expression

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

My old query looks like this:

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;

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;

But PostgreSQL tells me:

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
);

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.

Upvotes: 2

Views: 2509

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658082

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

Assumptions

  • You don't actually need the outer SELECT.

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

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

dm_name and rm_name mutually exclusive

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;

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.

dm_name and rm_name can coexist

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 inserts nothing if the SELECT does not returns row(s).

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.

Upvotes: 3

Related Questions