6.4. Возврат данных из измененных строк#
6.4. Возврат данных из измененных строк #
Иногда бывает полезно получить данные из измененных строк, пока они
обрабатываются. Команды INSERT, UPDATE,
DELETE и MERGE все имеют
необязательное предложение RETURNING, которое поддерживает это. Использование
RETURNING позволяет избежать выполнения дополнительного запроса к базе данных для
сбора данных и особенно ценно, когда в противном случае было бы
сложно надежно идентифицировать измененные строки.
Допустимое содержание предложения
RETURNING аналогично списку вывода команды SELECT
(см. Раздел 7.3). Оно может содержать имена столбцов целевой таблицы команды или выражения значений, использующие эти столбцы. Краткая запись RETURNING * выбирает все столбцы целевой таблицы по порядку.
В команде INSERT данные по умолчанию для RETURNING образуются из строки в том виде, в каком она была вставлена. Это не так удобно при обычных вставках, так как будут получены те же данные, что и предоставленные клиентом. Но это может быть очень удобно при использовании вычисляемых значений по умолчанию. Например, при использовании столбца serial для предоставления уникальных идентификаторов, RETURNING может вернуть идентификатор, присвоенный новой строке:
CREATE TABLE users (firstname text, lastname text, id serial primary key);
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
Предложение RETURNING также очень полезно
с INSERT ... SELECT.
В команде UPDATE данные по умолчанию, доступные для RETURNING, — это новое содержимое изменённой строки. Например:
UPDATE products SET price = price * 1.10 WHERE price <= 99.99 RETURNING name, price AS new_price;
В команде DELETE данные по умолчанию, доступные для RETURNING — это содержимое удалённой строки. Например:
DELETE FROM products WHERE obsoletion_date = 'today' RETURNING *;
В команде MERGE данные по умолчанию, доступные для RETURNING — это содержимое исходной строки, а также содержимое вставленной, обновлённой или удалённой целевой строки. Поскольку часто исходная и целевая строки имеют много одинаковых столбцов, указание RETURNING * может привести к большому количеству дублирующихся столбцов, поэтому зачастую лучше дополнить его, чтобы возвращалась только исходная или только целевая строка. Например:
MERGE INTO products p USING new_products n ON p.product_no = n.product_no WHEN NOT MATCHED THEN INSERT VALUES (n.product_no, n.name, n.price) WHEN MATCHED THEN UPDATE SET name = n.name, price = n.price RETURNING p.*;
В каждой из этих команд также возможно явно вернуть старое и новое содержимое изменённой строки. Например:
UPDATE products SET price = price * 1.10
WHERE price <= 99.99
RETURNING name, old.price AS old_price, new.price AS new_price,
new.price - old.price AS price_change;
В этом примере запись new.price эквивалентна просто price, но делает смысл более понятным.
Этот синтаксис для возврата старых и новых значений доступен в командах INSERT, UPDATE, DELETE и MERGE, но обычно старые значения будут NULL для INSERT, а новые значения будут NULL для DELETE. Однако существуют ситуации, когда это всё же может быть полезно и для этих команд. Например, в INSERT с предложением ON CONFLICT DO UPDATE старые значения будут не NULL для конфликтующих строк. Аналогично, если DELETE преобразуется в UPDATE с помощью правила перезаписи, новые значения могут быть не NULL.
Если на целевой таблице есть триггеры (Глава 35), то данные, доступные для RETURNING, представляют собой строку, измененную триггерами. Таким образом, проверка вычисляемых триггерами столбцов является еще одним распространенным случаем использования RETURNING.