如何在父表中插入单行，然后在 PostgreSQL 中的单个 SQL 中在子表中插入多行? [英] How to insert a single row in the parent table and then multiple rows in the child table in single SQL in PostgreSQL?
问题描述
请在我的架构下方找到:
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
);
我正在尝试使用可写 CTE 在一个 SQL 中插入发票及其发票项目.我目前坚持使用以下 SQL 语句:
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);
当然，这个 SQL 是错误的，这里是 PostgreSQL 9.2 关于它的说明:
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
有可能吗?
编辑 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) 不起作用 - 语法错误.
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.
编辑 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
);
这是我得到的:
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 语句 - 它有效！
Seems like the same error. It is interesting that if I remove all the UNION ALL and leave just one SELECT statement - it works!
编辑 3
为什么我必须强制转换参数?是否可以在 CTE 中指定列的类型?
Why do I have to cast the parameters? Is it possible to specify the type of columns in the CTE?
推荐答案
PostgreSQL 对 VALUES 子句有这样的扩展解释，它可以单独用作子查询.
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;
假设您要插入 new_invoice 的笛卡尔积和值，如果 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.
这篇关于如何在父表中插入单行，然后在 PostgreSQL 中的单个 SQL 中在子表中插入多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
