insert with multiple subinserts - reference return value from previous subinsert in a following subi-CodePudding

I have a series of tables, each referencing the serial id from the preceding one. Is there a way to insert into all of them with a single statement? I have tried

WITH ins1 AS ( INSERT INTO tab1 ( col ) VALUES ( 'val' ) RETURNING tab1.id AS tab1id ),
ins2 AS ( INSERT INTO tab2 ( col ) VALUES ( tab1id ) RETURNING tab2.id AS tab2id )
INSERT INTO finaltab ( col ) VALUES ( tab2id );

but WITH clause values are only available in the final insert.

Trying

WITH ins2 AS (
    INSERT INTO tab2 ( col )
    SELECT tab1id FROM (
        WITH ins1 AS (
            INSERT INTO tab1 ( col )
            VALUES ( 'val' )
            RETURNING tab1.id AS tab1id
        )
        SELECT tab1id
    )
    RETURNING tab2.id AS tab2id
)
INSERT INTO finaltab ( col ) VALUES ( tab2id );

does not work because data-modifying WITH clauses must be top-level.

Is there a way to do this?

CodePudding user response：

You need to select from the CTEs:

WITH ins1 AS ( 
  INSERT INTO tab1 (col) 
  VALUES ('val') 
  RETURNING tab1.id AS tab1id 
), ins2 AS ( 
  INSERT INTO tab2 (col) 
  select tab1id 
  from ins1
  RETURNING tab2.id AS tab2id 
)
INSERT INTO finaltab (col) 
select tab2id
from ins2;