ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Вопрос по INSERT INTO ... SELECT ... RETURNING
12 сообщений из 12, страница 1 из 1
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39082653
Фотография chernomyrdin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chernomyrdin
Участник
Столкнулся с проблемой, есть следующая структура данных для хранения информации о заказах:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
-- Собственно таблица заказов
CREATE TABLE site_order (
  id bigint NOT NULL DEFAULT nextval('id'::regclass),
  status integer,
  phone text,
  CONSTRAINT site_order_pk PRIMARY KEY (id)
);
-- Таблица состава заказа
CREATE TABLE site_order_item (
  id bigint NOT NULL DEFAULT nextval('id'::regclass),
  order_id bigint NOT NULL,
  name text,
  amount integer,
  cost real,
  CONSTRAINT site_order_item_pk PRIMARY KEY (id),
  CONSTRAINT site_order_item_fk_order FOREIGN KEY (order_id) REFERENCES site_order (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);
-- Таблица примечаний к составу заказа
CREATE TABLE site_order_item_detail
(
  id bigint NOT NULL DEFAULT nextval('id'::regclass),
  order_item_id bigint NOT NULL,
  detail text,
  CONSTRAINT site_order_item_detail_pk PRIMARY KEY (id),
  CONSTRAINT site_order_item_detail_fk_item FOREIGN KEY (order_item_id) REFERENCES site_order_item (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);



Есть данные:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
WITH o AS (
  INSERT INTO site_order (id,status,phone) VALUES (42,2,'+7 (000) 000-00-00')
  RETURNING *
),
item_list (name,amount,cost) AS (VALUES ('name 1', 1, 100), ('name 2', 1, 200), ('name 1', 3, 100)),
item AS (
  INSERT INTO site_order_item (order_id,name,amount,cost)
  SELECT o.id, v.name, v.amount, v.cost FROM o, item_list as v
  RETURNING *
),
detail_list (detail) AS (VALUES ('detail 1'), ('detail 2')),
detail AS (
  INSERT INTO site_order_item_detail (order_item_id, detail)
  SELECT oi.id, v.detail FROM (SELECT * FROM item ORDER BY random() LIMIT 1) as oi, detail_list as v
  RETURNING *
) SELECT * from o;



Хочется странного - сделать копию заказа вот таким образом:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
WITH o AS (
  INSERT INTO site_order (phone,status)
  SELECT phone,0 FROM site_order WHERE id=42 AND status=2
  RETURNING *
), item AS (
  INSERT INTO site_order_item (order_id,name,amount,cost)
  SELECT o.id,oi.name, oi.amount,oi.cost
  FROM o, site_order_item AS oi
  WHERE oi.order_id=42
  RETURNING *, o.id AS o_item_id  -- Это не работает, хотя хотелось-бы
), detail AS (
  INSERT INTO site_order_item_detail (order_item_id,detail)
  SELECT item.id, d.detil
  FROM item
  INNER JOIN site_order_item_detail AS d ON (d.order_item_id = item.o_item_id)
  RETURNING *  
) select * from o;



В теории можно добавить поле parent_id в теблицу site_order_item и в него заносить старые значения, но быть может можно обойтись без этого
...
Рейтинг: 0 / 0
21.10.2015, 16:48
| Ответить | Цитировать | Написать  
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39082696
Author the new one
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Author the new one
Участник
chernomyrdin,

А чего б не написать

Код: sql
1.
2.
3.
with item1 as(
 select i.*, i.order_id AS o_item_id
)


и так далее?
...
Рейтинг: 0 / 0
21.10.2015, 17:18
| Ответить | Цитировать | Написать  
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39082736
Фотография chernomyrdin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chernomyrdin
Участник
Вопрос в том, что мы получим после INSERT, как можно узнать какому старому значению id соответствует новое?
...
Рейтинг: 0 / 0
21.10.2015, 17:35
| Ответить | Цитировать | Написать  
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39082745
Author the new one
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Author the new one
Участник
chernomyrdin,

Так вы же вставляете o.id как site_order_item,order_id
А вообще потом заджоинить никто не мешает, если что-то еще надо.
...
Рейтинг: 0 / 0
21.10.2015, 17:39
| Ответить | Цитировать | Написать  
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39082758
Фотография chernomyrdin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chernomyrdin
Участник
Если разбить на части, получается вот что:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
…
old_item AS (
  SELECT * FROM site_order_item WHERE order_id = 42
),
new_item AS (
  INSERT INTO site_order_item (order_id)
  SELECT o.id, old_item.name, old_item.amount, old_item.cost
  FROM o, old_item
  RETURNING *
)
…



Дальше мы получить соответствие old_item.id => new_item.id, а вот как это сделать не понятно, можно влоб:

Код: plsql
1.
2.
3.
SELECT old_item.id AS o_item_id, new_item.id AS n_item_id
FROM old_item
LEFT JOIN new_item ON (new_item.name = old_item.name, new_item.amount = old_item.amount, …все поля)


Но это не работает в некоторых случаях.

Можно добавить колонку o_item_id в site_order_item и на этапе создании копии заполнять:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
item AS (
  INSERT INTO site_order_item (order_id,name,amount,cost,o_item_id)
  SELECT o.id,oi.name, oi.amount,oi.cost,oi.id
  FROM o, site_order_item AS oi
  WHERE oi.order_id=42
  RETURNING *
)



Но хотелось-бы обойтись без этой вспомогательной колонки, и все копирование выполнять одним запросом
...
Рейтинг: 0 / 0
21.10.2015, 17:51
| Ответить | Цитировать | Написать  
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39082769
Author the new one
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Author the new one
Участник
chernomyrdin,

Попробуйте делать временную таблицу, туда вставлять данные для order_item (и там же держать старый item_id), делать что надо и потом ее дропать.
...
Рейтинг: 0 / 0
21.10.2015, 18:03
| Ответить | Цитировать | Написать  
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39082776
Фотография chernomyrdin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chernomyrdin
Участник
Author the new one,

Про такой путь я так-же думал и возможно придется, если не найду другого выхода. Просто хотелось сделать подобную манипуляцию с минимумом телодвижений
...
Рейтинг: 0 / 0
21.10.2015, 18:10
| Ответить | Цитировать | Написать  
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39082778
Фотография chernomyrdin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chernomyrdin
Участник
Иллюстрация про JOIN двух таблиц.
Есть данные:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
  id   | order_id |  name  | amount | cost 
-------+----------+--------+--------+------
 10926 |       42 | name 1 |      1 |  100
 10927 |       42 | name 2 |      1 |  200
 10928 |       42 | name 1 |      3 |  100
 10929 |       42 | name 1 |      1 |  100
 10932 |       32 | name 1 |      1 |  100
 10933 |       32 | name 2 |      1 |  200
 10934 |       32 | name 1 |      3 |  100
 10935 |       32 | name 1 |      1 |  100
(8 rows)

Если сделать:
Код: plsql
1.
2.
3.
4.
5.
6.
select * from (
    select * from site_order_item where order_id=42
) as o42, (
  select * from site_order_item where order_id=32
) as o32
where o32.name = o42.name and o32.amount = o42.amount AND o32.cost = o42.cost;



Получаем не 4 строки а больше:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
  id   | order_id |  name  | amount | cost |  id   | order_id |  name  | amount | cost 
-------+----------+--------+--------+------+-------+----------+--------+--------+------
 10926 |       42 | name 1 |      1 |  100 | 10932 |       32 | name 1 |      1 |  100
 10926 |       42 | name 1 |      1 |  100 | 10935 |       32 | name 1 |      1 |  100
 10927 |       42 | name 2 |      1 |  200 | 10933 |       32 | name 2 |      1 |  200
 10928 |       42 | name 1 |      3 |  100 | 10934 |       32 | name 1 |      3 |  100
 10929 |       42 | name 1 |      1 |  100 | 10932 |       32 | name 1 |      1 |  100
 10929 |       42 | name 1 |      1 |  100 | 10935 |       32 | name 1 |      1 |  100
(6 rows)
...
Рейтинг: 0 / 0
21.10.2015, 18:13
| Ответить | Цитировать | Написать  
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39082781
Author the new one
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Author the new one
Участник
chernomyrdin,

ну так и сделайте, как я вам сразу и говорил

Код: sql
1.
2.
3.
4.
5.
item1 as(
 select nextval(...) as new_id, soi.* from site_order_item soi where soi.order_id=42
),
item as(insert into site_order_item(id, order_id, name, amount...) select i1.new_id, i1.order_id, .. from item1),
...
...
Рейтинг: 0 / 0
21.10.2015, 18:23
| Ответить | Цитировать | Написать  
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39083073
Фотография chernomyrdin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chernomyrdin
Участник
Ok, всем большое спасибо, вот что получилось:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
WITH o AS (
  INSERT INTO site_order (phone,status)
  SELECT phone,0 FROM site_order WHERE id=42 AND status=2
  RETURNING *
), item AS (
  SELECT
	nextval('id') AS id,
	o.id          AS order_id,
	oi.id         AS o_item_id,
	oi.name,
	oi.amount,
	oi.cost
  FROM o, site_order_item AS oi
  WHERE oi.order_id=42
), n_item AS (  
  INSERT INTO site_order_item (id,order_id,name,amount,cost)
  SELECT id,order_id,name,amount,cost FROM item 
  RETURNING *
), detail AS (
  INSERT INTO site_order_item_detail (order_item_id,detail)
  SELECT item.id, d.detail
  FROM item
  INNER JOIN site_order_item_detail AS d ON (d.order_item_id = item.o_item_id)
  RETURNING *  
) select * from o;


Выражение работает
...
Рейтинг: 0 / 0
22.10.2015, 09:40
| Ответить | Цитировать | Написать  
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39083222
crause
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
crause
Участник
не проще ли
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
-- Таблица состава заказа
CREATE TABLE site_order_item (
  id bigint NOT NULL DEFAULT nextval('id'::regclass),
  order_id bigint NOT NULL,
  name text,
  detail text, -- зачем делать отдельную таблицу?
  amount integer,
  cost real
);
...
Рейтинг: 0 / 0
22.10.2015, 11:32
| Ответить | Цитировать | Написать  
Вопрос по INSERT INTO ... SELECT ... RETURNING
    #39083667
Фотография chernomyrdin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chernomyrdin
Участник
crause,

Нет, реальная таблица получается сложнее, это для примера сделано упрощение.
...
Рейтинг: 0 / 0
22.10.2015, 14:49
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Вопрос по INSERT INTO ... SELECT ... RETURNING
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39082696&tid=1997689]:
 0ms
get settings:
 6ms
get forum list:
 13ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 184ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 49ms
get tp. blocked users:
 1ms
others: 280ms
total:  547ms
созд. / загр.: 547ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]