sql - 如何使用单个 PostgreSQL 查询进行深层复制？

Question

我有三个表:

CREATE TABLE offers
(
  id serial NOT NULL PRIMARY KEY,
  title character varying(1000) NOT NULL DEFAULT ''::character varying
);

CREATE TABLE items
(
  id serial NOT NULL PRIMARY KEY,
  offer_id integer NOT NULL,
  title character varying(1000) NOT NULL DEFAULT ''::character varying,
  CONSTRAINT items_offer_id_fkey FOREIGN KEY (offer_id)
      REFERENCES offers (id) 
);

CREATE TABLE sizes
(
  id serial NOT NULL PRIMARY KEY,
  item_id integer NOT NULL,
  title character varying(1000) NOT NULL DEFAULT ''::character varying,
  CONSTRAINT sizes_item_id_fkey FOREIGN KEY (item_id)
      REFERENCES items (id) 
);

我有 1 个包含 2 个项目的报价。每件商品有 2 种尺寸:

INSERT INTO offers (title) VALUES ('My Offer');
INSERT INTO items (offer_id, title) VALUES (1, 'First Item');
INSERT INTO items (offer_id, title) VALUES (1, 'Second Item');
INSERT INTO sizes (item_id, title) VALUES (1, 'First Size of Item #1');
INSERT INTO sizes (item_id, title) VALUES (1, 'Second Size of Item #1');
INSERT INTO sizes (item_id, title) VALUES (2, 'First Size of Item #2');
INSERT INTO sizes (item_id, title) VALUES (2, 'Second Size of Item #2');

有没有一种方法可以通过单个查询克隆包含所有商品和尺码的商品？

我尝试用 CTE 解决它，这是我的 SQL:

WITH tmp_offers AS (
    INSERT INTO offers (title)
    SELECT title FROM offers WHERE id = 1
    RETURNING id
), tmp_items AS (
    INSERT INTO items (offer_id, title)
    (SELECT (SELECT id FROM tmp_offers), title FROM items WHERE offer_id = 1)
    RETURNING id
)
INSERT INTO sizes (item_id, title)
(SELECT (SELECT id FROM tmp_items), title FROM sizes WHERE id IN (
    SELECT sizes.id FROM sizes
    JOIN items ON items.id = sizes.item_id
    WHERE items.offer_id = 1
));

但是这个 SQL 导致错误，我无法解决:

错误:用作表达式的子查询返回多于一行

非常感谢您的帮助。

附言我使用 PostgreSQL 9.5

Answer 1

这应该有效:

WITH tmp_offers AS (
    INSERT INTO offers (title)
    SELECT title 
    FROM offers 
    WHERE id = 1
    RETURNING id
), tmp_items AS (
    INSERT INTO items (offer_id, title)
    SELECT o.id, i.title 
    FROM items i
      cross join tmp_offers o
    WHERE i.offer_id = 1
    order by i.id
    RETURNING items.id
), numbered_new as (
  select ti.id, 
         row_number() over (order by ti.id) as rn
  from tmp_items ti
), numbered_old as (
  select i.id, 
         row_number() over (order by i.id) as rn
  from items i
  WHERE i.offer_id = 1
), item_mapper as (
  select n.id as new_item_id, 
         o.id as old_item_id
  from numbered_new n
     join numbered_old o on n.rn = o.rn
)
INSERT INTO sizes (item_id, title)
select im.new_item_id, s.title
from sizes s
  join item_mapper im on im.old_item_id = s.item_id;

在线示例:http://rextester.com/RYQUS11008