Available in: DSQL, ESQL, PSQL

Changed in: 2.0

Description

An INSERT … VALUES query may optionally specify a RETURNING clause in order to return the values that have actually been stored. The clause, if present, need not contain all of the insert columns 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.

Syntax

INSERT [TRANSACTION name]
   INTO {tablename | viewname} [(<columns>)]
   {VALUES (<values>) [RETURNING <columns> [INTO <variables>]]
   | select_expr}

<columns>      ::= colname [, colname ...]
<values>       ::= value [, value ...]
<variables>    ::= :varname [, :varname ...]
<select_expr>  ::= a SELECT returning a set whose columns fit the target

Restrictions

• The TRANSACTION directive is only available in ESQL.
• The RETURNING clause is not available in ESQL.
• The “INTO <variables>” subclause is only available in PSQL.
• The trigger context variables OLD and NEW must not be preceded by a colon (“:”).
• New in 2.0: No column may appear more than once in the insert list.

back to top of page

Available in: DSQL, PSQL

Added in: 2.0

Description

An INSERT query – unless it is SELECT-based – may optionally specify a RETURNING clause to produce a result set containing the values that have been actually stored. The clause, if present, need not contain all of the insert columns 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.

Example

insert into Scholars (firstname, lastname, address, phone, email)
   values ('Henry', 'Higgins', '27A Wimpole Street', '3231212', null)
   returning lastname, fullname, id

Note: In Firebird 2.0, the RETURNING clause is only supported for INSERT … VALUES queries. With INSERT … SELECT it is rejected, even if it concerns a singleton select. This limitation will be lifted in version 2.1.

back to top of page

Changed in: 2.0

Description

A SELECT query used in an INSERT statement may now be a UNION.

Example

insert into Members (number, name)
   select number, name from NewMembers where Accepted = 1
      union
   select number, name from SuspendedMembers where Vindicated = 1