The RETURNING
Clause
An UPDATE
statement may include RETURNING
to return some values from the updated rows.RETURNING
may include data from any column of the row, not only the columns that are updated by the statement.It can include literals or expressions not associated with columns, if there is a need for that.
The user executing the statement needs to have SELECT
privileges on the columns specified in the RETURNING
clause.
When the RETURNING
set contains data from the current row, the returned values report changes made in the BEFORE UPDATE
triggers, but not those made in AFTER UPDATE
triggers.
The context variables OLD.fieldname
and NEW.fieldname
can be used as column names.If OLD.
or NEW.
is not specified, or if the table name (target) is specified instead, the column values returned are the NEW.
ones.
The syntax of the returning_list is similar to the column list of a SELECT
clause.It is possible to reference all columns using *
, or table_name.*
, NEW.*
and/or OLD.*
.
In DSQL, a positioned update statement (WHERE CURRENT OF …
) with RETURNING
always returns a single row, a normal update statement can return zero or more rows.The update is executed to completion before rows are returned.In PSQL, attempts to execute an UPDATE … RETURNING
that affects multiple rows will result in the error “multiple rows in singleton select”.This behaviour may change in a future Firebird version.