The

CommentsProcedural SQL (PSQL) Statements

The `RETURNING` Clause

An INSERT statement may optionally include a RETURNING clause to return values from the inserted rows.The clause, if present, need not contain all columns referenced in the insert statement and may also contain other columns or expressions.The returned values reflect any changes that may have been made in BEFORE INSERT triggers.

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.*.

The optional INTO sub-clause is only valid in PSQL.

Note	Caveats for updatable views The values reported by `RETURNING` for updatable views may be incorrect if the view is made updatable through triggers.See [fblangref50-ddl-view-updatable-returning].

Important

Multiple INSERTs

In DSQL, an INSERT … VALUES (…) RETURNING or INSERT … DEFAULT VALUES RETURNING returns only one row, and a INSERT … SELECT … RETURNING can return zero or more rows.

In PSQL, if the RETURNING clause is specified and more than one row is inserted by the INSERT statement, the statement fails and a “multiple rows in singleton select” error is returned.This behaviour may change in future Firebird versions.

Examples

INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES ('Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING lastname, fullname, id;

INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING *;

INSERT INTO Dumbbells (firstname, lastname, iq)
  SELECT fname, lname, iq
FROM Friends
  ORDER BY iq ROWS 1
  RETURNING id, firstname, iq
INTO :id, :fname, :iq;

Note

In DSQL, an INSERT … VALUES (…) RETURNING always returns exactly one row.This behaviour may change in a future Firebird version.
In DSQL, an INSERT … DEFAULT VALUES RETURNING always returns exactly one row.
In DSQL, an INSERT … SELECT … RETURNING returns zero or more rows.
In PSQL, if multiple rows are returned, the statement fails with a “multiple rows in singleton select” error.This behaviour may change in a future Firebird version.
In PSQL, if no row was inserted, nothing is returned, and the target variables keep their existing values.

Inserting into `BLOB` columns

Inserting into BLOB columns is only possible under the following circumstances:

The client application has made special provisions for such inserts, using the Firebird API.In this case, the modus operandi is application-specific and outside the scope of this manual.

The value inserted is a string literal of no more than 65,533 bytes (64KB - 3).

Note	A limit, in characters, is calculated at run-time for strings that are in multi-byte character sets, to avoid overrunning the bytes limit.For example, for a UTF8 string (max. 4 bytes/character), the run-time limit is likely to be about (floor(65533/4)) = 16383 characters.

You are using the “INSERT … SELECT” form and one or more columns in the result set are BLOBs.

The

The RETURNING Clause

Inserting into BLOB columns

The `RETURNING` Clause

Inserting into `BLOB` columns