How to insert a single row in the parent table and then multiple rows in the child table in single SQL in PostgreSQL?

Question

Please, find below my schema:

CREATE TABLE reps (
  id    SERIAL PRIMARY KEY,
  rep   TEXT NOT NULL UNIQUE
);

CREATE TABLE terms (
  id    SERIAL PRIMARY KEY,
  terms TEXT NOT NULL UNIQUE
);

CREATE TABLE shipVia (
  id        SERIAL PRIMARY KEY,
  ship_via  TEXT NOT NULL UNIQUE
);

CREATE TABLE invoices (
  id                    SERIAL PRIMARY KEY,
  customer              TEXT NOT NULL CONSTRAINT customerNotEmpty CHECK(customer <> ''),
  term_id               INT REFERENCES terms,
  rep_id                INT NOT NULL REFERENCES reps,
  ship_via_id           INT REFERENCES shipVia,
  ...
  item_count            INT NOT NULL,
  modified              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  version               INT NOT NULL DEFAULT 0
);

CREATE TABLE invoiceItems (
  id            SERIAL PRIMARY KEY,
  invoice_id    INT NOT NULL REFERENCES invoices ON DELETE CASCADE,
  name          TEXT NOT NULL CONSTRAINT nameNotEmpty CHECK(name <> ''),
  description   TEXT,
  qty           INT NOT NULL CONSTRAINT validQty CHECK (qty > 0),
  price         DOUBLE PRECISION NOT NULL
);

I am trying to insert an invoice along with its invoice items in one SQL using writable CTE. I am currently stuck with the following SQL statement:

WITH new_invoice AS (
    INSERT INTO invoices (id, customer, term_id, ship_via_id, rep_id, ..., item_count)
    SELECT $1, $2, t.id, s.id, r.id, ..., $26
    FROM reps r
    JOIN terms t ON t.terms = $3
    JOIN shipVia s ON s.ship_via = $4
    WHERE r.rep = $5
    RETURNING id
) INSERT INTO invoiceItems (invoice_id, name, qty, price, description) VALUES
 (new_invoice.id,$27,$28,$29,$30)
,(new_invoice.id,$31,$32,$33,$34)
,(new_invoice.id,$35,$36,$37,$38);

Of course, this SQL is wrong, here is what PostgreSQL 9.2 has to say about it:

ERROR:  missing FROM-clause entry for table "new_invoice"
LINE 13:  (new_invoice.id,$27,$28,$29,$30)
           ^


********** Error **********

ERROR: missing FROM-clause entry for table "new_invoice"
SQL state: 42P01
Character: 704

Is it possible at all?

EDIT 1

I am trying the following version:

PREPARE insert_invoice_3 AS WITH 
new_invoice AS (
    INSERT INTO invoices (id, customer, term_id, ship_via_id, rep_id, ..., item_count) 
    SELECT $1, $2, t.id, s.id, r.id, ..., $26
    FROM reps r
    JOIN terms t ON t.terms = $3
    JOIN shipVia s ON s.ship_via = $4
    WHERE r.rep = $5
    RETURNING id
),
v (name, qty, price, description) AS (
    VALUES ($27,$28,$29,$30)
          ,($31,$32,$33,$34)
          ,($35,$36,$37,$38)
) 
 INSERT INTO invoiceItems (invoice_id, name, qty, price, description)
 SELECT new_invoice.id, v.name, v.qty, v.price, v.description
 FROM v, new_invoice;

And here is what I get in return:

ERROR:  column "qty" is of type integer but expression is of type text
LINE 19:  SELECT new_invoice.id, v.name, v.qty, v.price, v.descriptio...
                                         ^
HINT:  You will need to rewrite or cast the expression.

********** Error **********

ERROR: column "qty" is of type integer but expression is of type text
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
Character: 899

I guess v (name, qty, price, description) is not enough, the data types must be specified as well. However, v (name, qty INT, price, description) does not work - syntax error.

EDIT 2

Next, I have just tried the second version:

PREPARE insert_invoice_3 AS WITH 
new_invoice AS (
        INSERT INTO invoices (id, customer, term_id, ship_via_id, rep_id, ..., item_count) 
        SELECT $1, $2, t.id, s.id, r.id, ..., $26
        FROM reps r
        JOIN terms t ON t.terms = $3
        JOIN shipVia s ON s.ship_via = $4
        WHERE r.rep = $5
        RETURNING id
) 
 INSERT INTO invoiceItems (invoice_id, name, qty, price, description)
(
 SELECT i.id, $27, $28, $29, $30 FROM new_invoice i
 UNION ALL
 SELECT i.id, $31, $32, $33, $34 FROM new_invoice i
 UNION ALL
 SELECT i.id, $35, $36, $37, $38 FROM new_invoice i
);

Here is what I get:

ERROR:  column "qty" is of type integer but expression is of type text
LINE 15:  SELECT i.id, $27, $28, $29, $30 FROM new_invoice i
                            ^
HINT:  You will need to rewrite or cast the expression.

********** Error **********

ERROR: column "qty" is of type integer but expression is of type text
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
Character: 759

Seems like the same error. It is interesting that if I remove all the UNION ALL and leave just one SELECT statement - it works!

EDIT 3

Why do I have to cast the parameters? Is it possible to specify the type of columns in the CTE?

Answer 1

PostgreSQL has such an extended interpretation of the VALUES clause that it may be used as a subquery by itself.

So you may express your query in this form:

WITH new_invoice AS (
    INSERT INTO ...
    RETURNING id
),
v(a,b,c,d) AS (values
  ($27,$28,$29,$30),
  ($31,$32,$33,$34),
  ...
)
INSERT INTO invoiceItems (invoice_id, name, qty, price, description)
 SELECT new_invoice.id, a,b,c,d FROM v, new_invoice;

That assumes you want to insert the cartesian product of new_invoice and the values, which mostly makes sense if new_invoice is actually a single-row value.

Answer 2

WITH new_invoice AS (
    INSERT INTO invoices ...
    RETURNING id
)

INSERT INTO invoiceItems (invoice_id, name, qty, price, description)
VALUES ((select id from new_invoice), $27 , $28, $29,   $30),
       ((select id from new_invoice), $31 , $32, $33,   $34),
       ((select id from new_invoice), $35 , $36, $37,   $38);

Answer 3

Instead of insert ... values ...., use insert ... select ...:

) INSERT INTO invoiceItems (invoice_id, name, qty, price, description)
SELECT new_invoice.id,$27,$28,$29,$30 FROM new_invoice
UNION ALL
...