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

概述请在我的架构下面找到： 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 ( 请在我的架构下面找到：

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 ,,t.ID,s.ID,r.ID,    FROM reps r    JOIN terms t ON t.terms =     JOIN shipVia s ON s.ship_via =     WHERE r.rep =     RETURNING ID) INSERT INTO invoiceItems (invoice_ID,name,qty,price,description) VALUES (new_invoice.ID,,,,),(new_invoice.ID,,,,),,,,);

当然,这个sql是错误的,这就是Postgresql 9.2对它的看法：

ERROR:  missing FROM-clause entry for table "new_invoice"liNE 13:  (new_invoice.ID,)           ^********** Error **********ERROR: missing FROM-clause entry for table "new_invoice"sql state: 42P01Character: 704

有可能吗？

编辑1

我正在尝试以下版本：

PREPARE insert_invoice_3 AS WITH new_invoice AS (    INSERT INTO invoices (ID,item_count)     SELECT ,    FROM reps r    JOIN terms t ON t.terms =     JOIN shipVia s ON s.ship_via =     WHERE r.rep =     RETURNING ID),v (name,description) AS (    VALUES (,(,(,))  INSERT INTO invoiceItems (invoice_ID,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 textliNE 19:  SELECT new_invoice.ID,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 textsql state: 42804Hint: You will need to rewrite or cast the Expression.Character: 899

我猜v(名称,数量,价格,描述)是不够的,还必须指定数据类型.但是,v(名称,数量INT,描述)不起作用 – 语法错误.

编辑2

接下来,我刚刚尝试了第二个版本：

PREPARE insert_invoice_3 AS WITH new_invoice AS (        INSERT INTO invoices (ID,item_count)         SELECT ,        FROM reps r        JOIN terms t ON t.terms =         JOIN shipVia s ON s.ship_via =         WHERE r.rep =         RETURNING ID)  INSERT INTO invoiceItems (invoice_ID,description)( SELECT i.ID, FROM new_invoice i UNION ALL SELECT i.ID, FROM new_invoice i UNION ALL SELECT i.ID, FROM new_invoice i);

这是我得到的：

ERROR:  column "qty" is of type integer but Expression is of type textliNE 15:  SELECT i.ID, 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 textsql state: 42804Hint: You will need to rewrite or cast the Expression.Character: 759

似乎是同样的错误.有趣的是,如果我删除所有UNION ALL并只留下一个SELECT语句 – 它可以工作！

编辑3

为什么我要投射参数？是否可以在CTE中指定列的类型？

解决方法 Postgresql对VALUES子句有如此扩展的解释,它可以单独用作子查询.

因此,您可以用以下形式表达您的查询：

WITH new_invoice AS (    INSERT INTO ...    RETURNING ID),v(a,b,c,d) AS (values  (,...)INSERT INTO invoiceItems (invoice_ID,a,d FROM v,new_invoice;

这假设您想要插入new_invoice的笛卡尔积和值,如果new_invoice实际上是单行值,这通常是有意义的.

总结

以上是内存溢出为你收集整理的如何在父表中插入单行,然后在PostgreSQL中的单个SQL中插入子表中的多行？全部内容，希望文章能够帮你解决如何在父表中插入单行,然后在PostgreSQL中的单个SQL中插入子表中的多行？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。