sql - PostgreSQL nested INSERTs / WITHs for foreign key insertions

Question

I'm using PostgreSQL 9.3, and I'm trying to write a SQL script to insert some data for unit tests, and I've run into a bit of a problem.

Let's say we have three tables, structured like this:

------- Table A -------    -------- Table B --------    -------- Table C --------
id  | serial NOT NULL      id   | serial NOT NULL       id   | serial NOT NULL
foo | character varying    a_id | integer NOT NULL      b_id | integer NOT NULL
                           bar  | character varying     baz  | character varying

The columns B.a_id and C.b_id are foreign keys to the id column of tables A and B, respectively.

What I'm trying to do is to insert a row into each of these three tables with pure SQL, without having the ID's hard-coded into the SQL (making assumptions about the database before this script is run seems undesirable, since if those assumptions change I'll have to go back and re-compute the proper ID's for all of the test data).

Note that I do realize I could do this programatically, but in general writing pure SQL is way less verbose than writing program code to execute SQL, so it makes more sense for test suite data.

Anyway, here's the query I wrote which I figured would work:

WITH X AS (
    WITH Y AS (
        INSERT INTO A (foo)
        VALUES ('abc')
        RETURNING id
    )
    INSERT INTO B (a_id, bar)
    SELECT id, 'def'
    FROM Y
    RETURNING id
)
INSERT INTO C (b_id, baz)
SELECT id, 'ghi'
FROM X;

However, this doesn't work, and results in PostgreSQL telling me:

ERROR:  WITH clause containing a data-modifying statement must be at the top level

Is there a correct way to write this type of query in general, without hard-coding the ID values?

(You can find a fiddle here which contains this example.)

Answer 1

Don't nest the common table expressions, just write one after the other:

WITH Y AS (
  INSERT INTO A (foo)
  VALUES ('abc')
  RETURNING id
), x as (
  INSERT INTO B (a_id, bar)
  SELECT id, 'def'
  FROM Y
  RETURNING id
)
INSERT INTO C (b_id, baz)
SELECT id, 'ghi'
FROM X;