postgresql - 如何在 PostgreSQL 的单个 SQL 中在父表中插入单行，然后在子表中插入多行？

Question

请在下面找到我的架构:

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

我正在尝试使用可写 CTE 在一个 SQL 中插入一张发票及其发票项目。我目前坚持使用以下 SQL 语句:

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

当然，这个 SQL 是错误的，这是 PostgreSQL 9.2 对它的看法:

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

有可能吗？

编辑 1

我正在尝试以下版本:

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;

这是我得到的返回:

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

我想 v (name, qty, price, description) 是不够的，还必须指定数据类型。但是，v (name, quty INT, price, description) 不起作用 - 语法错误。

编辑 2

接下来，我刚试过第二个版本:

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

这是我得到的:

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

似乎是同样的错误。有趣的是，如果我删除所有 UNION ALL 并只留下一个 SELECT 语句 - 它有效!

编辑 3

为什么我必须转换参数？是否可以在 CTE 中指定列的类型？

Answer 1

PostgreSQL 对 VALUES 子句有这样的扩展解释，它可以单独用作子查询。

所以你可以用这种形式表达你的查询:

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;

假设您要插入 new_invoice 和值的笛卡尔积，如果 new_invoice 实际上是单行值，这很有意义。