ВОЗВРАЩАЕМЫЕ ЗНАЧЕНИЯ

1.Обзор

returning-clause: скрыть

Клауза RETURNING не является самостоятельным оператором, а клаузой, которая может необязательно появиться в конце операторов DELETE, INSERT и UPDATE верхнего уровня. Действие клаузы RETURNING заключается в том, чтобы заставить оператор возвращать одну строку результата для каждой строки базы данных, которая удаляется, вставляется или обновляется. RETURNING не является стандартным SQL. Это расширение. Синтаксис RETURNING в SQLite смоделирован по образцу 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 ссылки на столбцы в изменяемой таблице относятся к значению этого столбца после применения изменения. Для операторов DELETE ссылки на столбцы означают значение до удаления.

Клауза 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-значения, то оператор может использовать много временной памяти для хранения этих значений во время выполнения.

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

2.2. Самоссылочные подзапросы не определены

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

Следовательно, если столбец вывода RETURNING содержит подзапрос, ссылающийся на изменяемую таблицу, то результат этого подзапроса может зависеть от неопределенного поведения и, следовательно, может изменяться от одного вызова запроса к другому.

2.3. Изменения 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.