The RETURNING
Clause
The optional RETURNING
clause, if present, need not contain all the columns mentioned in the statement and may also contain other columns or expressions.The returned values reflect any changes that may have been made in BEFORE
triggers, but not those in AFTER
triggers.OLD.fieldname
and NEW.fieldname
may both be used in the list of columns to return;for field names not preceded by either of these, the new value is returned.
The user executing the statement needs to have SELECT
privileges on the columns specified in the RETURNING
clause.
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 statement with a RETURNING
clause can return zero or more rows.The update or insert is executed to completion before rows are returned.In PSQL, if a RETURNING
clause is present and more than one matching record is found, an error “multiple rows in singleton select” is raised.This behaviour may change in a future Firebird version.
The optional INTO
sub-clause is only valid in PSQL.