FirebirdSQL logo

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.

Example of UPDATE OR INSERT

Modifying data in a table, using UPDATE OR INSERT in a PSQL module.The return value is passed to a local variable, whose colon prefix is optional.

UPDATE OR INSERT INTO Cows (Name, Number, Location)
  VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
  MATCHING (Number)
  RETURNING rec_id into :id;

UPDATE OR INSERT INTO Cows (Name, Number, Location)
  VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
  MATCHING (Number)
  RETURNING old.*, new.*;

DELETE

Deletes rows from a table or updatable view

Syntax
DELETE
  FROM target [[AS] alias]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value-expression>
  | [target.]col_name

<value-expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::=
  [:]varname [, [:]varname ...]
Table 1. Arguments for the DELETE Statement Parameters
Argument Description

target

The name of the table or view from which the records are to be deleted

alias

Alias for the target table or view

search-conditions

Search condition limiting the set of rows being targeted for deletion

cursorname

The name of the cursor in which current record is positioned for deletion

plan_items

Query plan clause

sort_items

ORDER BY clause

m, n

Integer expressions for limiting the number of rows being deleted

return_expression

An expression to be returned in the RETURNING clause

value-expression

An expression whose value is used for returning

varname

Name of a PSQL variable

DELETE removes rows from a database table or from one or more of the tables that underlie a view.WHERE and ROWS clauses can limit the number of rows deleted.If neither WHERE nor ROWS is present, DELETE removes all the rows in the relation.

Aliases

If an alias is specified for the target table or view, it must be used to qualify all field name references in the DELETE statement.

Examples

Supported usage:

delete from Cities where name starting 'Alex';

delete from Cities where Cities.name starting 'Alex';

delete from Cities C where name starting 'Alex';

delete from Cities C where C.name starting 'Alex';

Not possible:

delete from Cities C where Cities.name starting 'Alex';