请看下面的架构图：
当然，这个SQL是错误的，以下是PostgreSQL 9.2对此的解释：
这是我得到的内容：
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, qty 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中指定列的类型？
VALUES ($27,$28::integer,$29::integer,$30)，但会出现以下错误：ERROR: could not determine data type of parameter $6。$6似乎未被使用。顺便说一下，我认为你有很多参数，而27到38似乎也是一个重复的组。 - wildplasser