1. Обзор

returning-clause: hide

RETURNING это не сам запрос, а пункт, который может произвольно появиться около конца DELETE, INSERT и UPDATE верхнего уровня. Эффект пункта RETURNING состоит в том, чтобы заставить запрос вернуть одну строку результата для каждой строки базы данных, которая удалена, вставлена или обновлена. RETURNING это не стандартный SQL. Это расширение. Синтаксис SQLITE для RETURNING смоделирован после PostgreSQL.

Синтаксис RETURNING был поддержан SQLite начиная с версии 3.35.0 (2021-03-12).

1.1. Типичное использование

Пункт RETURNING разработан, чтобы предоставить значения колонок, которые заполнены SQLite автоматически. Например:

CREATE TABLE t0(a INTEGER PRIMARY KEY,
                b DATE DEFAULT CURRENT_TIMESTAMP, c INTEGER);
INSERT INTO t0(c) VALUES(random()) RETURNING *;

В INSERT выше SQLite вычисляет значения для всех трех колонок. Пункт RETURNING заставляет SQLite докладывать выбранные значения приложению. Это избавляет от необходимости выпустить отдельный запрос, чтобы выяснить точно, какие значения были вставлены.

2. Детали

Пункт RETURNING сопровождается списком разделенных запятой значений выражений. Эти выражения подобны выражениям после ключевого слова SELECT в операторе SELECT, они определяют значения колонок в наборе результатов. Каждое выражение определяет значение для отдельного столбца. Каждое выражение может произвольно сопровождаться пунктом AS, который определяет название столбца результата. Специальное выражение "*" расширяется в список всех явных удаляемых, вставленных или обновленных столбцов таблицы.

Для INSERT и UPDATE ссылки на колонки в изменяемой таблице относятся к значениям той колонки после того, как изменение было применено. Для операторов удаления ссылки на колонки означают значение прежде, чем удаление произойдет.

Пункт RETURNING возвращает только строки, которые непосредственно изменяются DELETE, INSERT или UPDATE. Пункт RETURNING не сообщает ни о каких дополнительных изменениях базы данных, вызванных ограничениями внешнего ключа или триггерами.

Пункт RETURNING для UPSERT сообщает и о вставленных и об обновленных строках.

2.1. Порядок работы

Когда DELETE, INSERT или UPDATE с пунктом RETURNING работают, все изменения базы данных происходят во время первого вызова sqlite3_step(). Вывод RETURNING накоплен в памяти. Первый вызов sqlite3_step() возвращает одну строку вывода RETURNING, последующие строки вывода RETURNING возвращены последующими sqlite3_step(). Чтобы поместить это иначе, на весь вывод RETURNING накладывают блокировку, пока все действия модификации базы данных не закончены.

Это означает, что, если у запроса есть пункт RETURNING, который производит большую сумму вывода, много строк, большие строки или BLOB, запрос мог бы использовать много временной памяти, чтобы содержать те значения при работе.

Первый прототип пункта RETURNING возвратил значения, когда они были произведены. Тот подход использовал меньше памяти, но у этого были другие проблемы:

1. Если вызовы sqlite3_step() для двух или больше запросов DML чередованы и если один из запросов получил ограничительную неудачу и прерван, возвращая изменения, то это могло разрушить работу другого запроса DML. Это не могло испортить файл базы данных, но может привести к удивлению и трудно объясняемым результатам в базе данных.

2. Если бы приложение не вызвало sqlite3_step() неоднократно, пока это не получило SQLITE_DONE, то некоторые изменения базы данных никогда не могли бы происходить.

3. Порядок операций отличается от других ядер базы данных клиент-сервер, например, PostgreSQL, что могло бы вызвать проблемы мобильности для некоторых приложений.

По этим причинам было изменено текущее внедрение так, чтобы все изменения базы данных произошли, прежде чем любой вывод RETURNING выдан.

В то время как SQLite действительно гарантирует, что все изменения базы данных произойдут, прежде чем любой вывод RETURNING выдан, это не гарантирует, что порядок отдельных строк RETURNING будет соответствовать порядку, в котором те строки были изменены в базе данных. Порядок вывода строк RETURNING произволен и не обязательно связан с порядком, в котором они были обработаны внутренне.

2.2. Изменения ACID

Когда предыдущая секция говорит, что "изменения базы данных происходят во время первого вызова sqlite3_step()", это означает, что изменения сохранены в частном кэше страницы соединения с базой данных, которое управляет запросом. Это не означает, что изменения на самом деле передаются. Передача не происходит до конца запроса и возможно даже тогда, если запрос это часть большей транзакции. Изменения базы данных все еще атомные, последовательные, изолированные и длительные (ACID). Когда предыдущая секция говорит, что "изменения происходят", это означает, что внутренняя структура данных приспособлена, ожидая передачу транзакции. Некоторые из тех изменений могут или не могут проникнуть в журнал с упреждающей записью, в зависимости от объема данных в кэше страницы. Если кэш не будет находиться под давлением памяти, то, вероятно, ничто не будет написано на диск пока транзакция не закончится, то есть sqlite3_step() вернет SQLITE_DONE.

Другими словами, когда предыдущая секция говорит, что "изменения базы данных происходят", это означает, что изменения происходят в памяти об определенном соединении с базой данных, которое управляет запросом, а не то, что изменения записаны на диск.

3. Ограничения

1. Пункт RETURNING недоступен в DELETE и UPDATE на виртуальных таблицах. Это ограничение могло бы быть удалено в будущих версиях SQLite.

2. Пункт RETURNING только доступен в DELETE, INSERT и UPDATE верхнего уровня. RETURNING не может использоваться запросами в триггерах.

3. Даже при том, что запрос DML с RETURNING возвращает содержание, оно не может использоваться в качестве подзапроса. Пункт RETURNING может только возвратить данные к применению. В настоящее время невозможно отклонить вывод RETURNING в другую таблицу или запрос. У PostgreSQL есть способность использовать запрос DML с пунктом RETURNING как представление в общем выражении таблицы. У SQLite в настоящее время нет той способности, хотя это могло бы быть добавлено в будущем выпуске.

4. Строки, возвращенные RETURNING, появляются в произвольном порядке. Тот порядок мог бы измениться в зависимости от схемы базы данных после определенного выпуска SQLite или даже от одного выполнения того же самого запроса. Нет никакого способа заставить строки появляться в особом порядке. Даже если SQLite собран с выбором SQLITE_ENABLE_UPDATE_DELETE_LIMIT таким образом, что пункты ORDER BY позволены в DELETE и UPDATE, те пункты ORDER BY не ограничивают порядок вывода RETURNING.

5. Значения RETURNING являются значениями DELETE, INSERT или UPDATE верхнего уровня и не отражают последующих изменений значений, внесенных триггерами. Таким образом, если база данных включает триггер AFTER, который изменяет некоторые значения каждой вставленной или обновленной строки, RETURNING выдает исходные значения, которые вычисляются перед теми триггерами.

6. Пункт RETURNING не может содержать агрегатные функции верхнего уровня или функции окна. Если есть подзапросы в пункте RETURNING, те подзапросы могут содержать совокупности и функции окна, но совокупности не могут произойти на верхнем уровне.

7. Пункт RETURNING может сослаться только на изменяемую таблицу. В UPDATE FROM вспомогательные таблицы в FROM не могут участвовать в пункте RETURNING.