RETURNING

RETURNING is available in SDK 5.1 and later.

The optional RETURNING clause is accepted by the three DML (Data Manipulation Language) statements — INSERT, UPDATE and DELETE/EVICT/TOMBSTONE — and by no other statement. A DML statement normally reports only the IDs of the documents it changed. Adding RETURNING turns it into a statement that returns a result set built from the affected documents, in the same form as a SELECT statement, so you can read the affected data back without issuing a second query.

Documents Projected

Each statement projects the affected documents at a different point in its processing:

Statement	Documents projected
`INSERT`	The documents as written, after the insert. Documents skipped by `ON ID CONFLICT DO NOTHING` are not written, so are not projected.
`UPDATE`	The documents as they are after the `APPLY`, `SET` and `UNSET` mutators have been applied.
`DELETE`/`EVICT`/`TOMBSTONE`	The documents as they were immediately before removal.

A statement populates either its result set (when RETURNING is present) or its list of mutated document IDs (when it is not), never both.

Projection Syntax

RETURNING takes the same projection syntax as a SELECT statement, with these differences:

DISTINCT may not be specified. The word is taken to be a field name rather than a keyword.
RAW projections are not supported.
The unqualified * wildcard is only accepted as the sole element of the projection list; combining it with further elements is an error. A qualified alias.* has no such restriction.
Aggregates are permitted and are evaluated over the whole set of affected documents; there is no GROUP BY clause. When any element of the projection is an aggregate, the remaining elements must not depend on the documents, otherwise an error is raised.

Default aliases are assigned, and uniqueness of aliases is required, exactly as for a SELECT projection.

Examples

Return the complete documents that were inserted:

DQL

INSERT INTO cars DOCUMENTS ({"_id": "123", "color": "blue"})
RETURNING *

Return selected fields of the documents that were updated, with their post-update values:

DQL

UPDATE cars
SET color = 'red'
WHERE color = 'blue'
RETURNING _id, color

Return the documents that were deleted, as they were immediately before removal:

DQL

DELETE FROM cars
USE IDS '123', '456'
RETURNING *

Return an aggregate over the affected documents rather than the documents themselves:

DQL

DELETE FROM cars
WHERE year < 2000
RETURNING COUNT(*) AS removed

Use an expression and an alias, exactly as in a SELECT projection:

DQL

UPDATE cars
APPLY odometer INCREMENT BY 100
WHERE _id = '123'
RETURNING _id, odometer / 1.60934 AS mileage

RETURNING and Legacy DML

RETURNING is implemented by the query engine’s DML operators only. The legacy (non-operator-model) implementation does not support it, so a statement that combines the two is rejected with the error RETURNING clause with legacy DML is not supported. Legacy handling is selected in either of two ways:

the #disable_dml directive on an individual statement, which disables RETURNING for that statement only; or
the DQL_USE_LEGACY_DML system parameter, which does the same for every statement.

The same restriction applies to the rest of the syntax that only the operator model implements: USE IDS on UPDATE and DELETE/EVICT/TOMBSTONE, and INSERT sourced from a SELECT.

DQL_USE_LEGACY_DML defaults to false in the Edge SDK (small peer) and true on Ditto Server (big peer). RETURNING is therefore available by default in the SDK, and on Ditto Server only where that parameter has been set to false.

Statements

Types and Functions

Reference

Documents Projected

Projection Syntax

Examples

RETURNING and Legacy DML

See Also

​Documents Projected

​Projection Syntax

​Examples

​RETURNING and Legacy DML

​See Also

Documents Projected

Projection Syntax

Examples

RETURNING and Legacy DML

See Also