INSERT RETURNING with values other than inserted ones.

Поиск
Список
Период
Сортировка
От Aleksandr Furmanov
Тема INSERT RETURNING with values other than inserted ones.
Дата
Msg-id C47F63F6-B959-4A22-9F37-7A43950EB2EA@nationbuilder.com
обсуждение исходный текст
Ответы Re: INSERT RETURNING with values other than inserted ones.  (Richard Dunks <richarddunks@gmail.com>)
Список pgsql-general
Hello,
I want to insert new values into target table 'a' from source table 'b', and then update table 'b' with ids from table
'a',somewhat like: 

CREATE TABLE a(id SERIAL, name TEXT);
INSERT INTO a (name) VALUES('Jason');
INSERT INTO a (name) VALUES('Peter');

CREATE TABLE b(row_id serial, id INT, name TEXT);
INSERT INTO b (name) VALUES('Jason');
INSERT INTO b (name) VALUES('Peter');


WITH inserted AS (INSERT INTO a (name) SELECT b.name FROM b WHERE b.name = name RETURNING a.id)
  UPDATE b SET id = inserted.id FROM inserted WHERE inserted.row_id = b.row_id;

However this would not work for obvious reason:

WHERE inserted.row_id = b.row_id is invalid because RETURNING clause cannot return row_id.
What can be returned are only columns of 'a', but they are insufficient to identify matching records of 'b'.

So the question is - what to put in WHERE clause to match RETURNING with rows being inserted from 'b'?

Thanks!

--
Aleksandr Furmanov



В списке pgsql-general по дате отправления:

Предыдущее
От: John R Pierce
Дата:
Сообщение: Re: databse version
Следующее
От: Richard Dunks
Дата:
Сообщение: Re: INSERT RETURNING with values other than inserted ones.