| 123456789101112131415161718192021222324252627282930313233343536 |
- ----------------
- RETURNING clause
- ----------------
- Function:
- Allow to return the column values actually stored in the table as a result of the INSERT statement.
- The most common usage is to retrieve the value of the primary key generated inside a BEFORE-trigger.
- Author:
- Dmitry Yemanov <[email protected]>
- Syntax rules:
- INSERT INTO ... VALUES (...) [RETURNING <column_list> [INTO <variable_list>]]
- Scope:
- DSQL, PSQL
- Example(s):
- 1. INSERT INTO T1 (F1, F2)
- VALUES (:F1, :F2)
- RETURNING F1, F2 INTO :V1, :V2;
- 2. INSERT INTO T2 (F1, F2)
- VALUES (1, 2)
- RETURNING ID INTO :PK;
- Note(s):
- 1. The INTO part (i.e. the variable list) is allowed in PSQL only (to assign local variables)
- and rejected in DSQL.
- 2. In DSQL, values are being returned within the same protocol roundtrip as the INSERT itself
- is executed.
- 3. If the RETURNING clause is present, then the statement is described as
- isc_info_sql_stmt_exec_procedure by the API (instead of isc_info_sql_stmt_insert),
- so the existing connectivity drivers should support this feature automagically.
- 4. Any explicit record change (update or delete) performed by AFTER-triggers is ignored by
- the RETURNING clause.
- 5. Cursor based inserts (INSERT INTO ... SELECT ... RETURNING ...) are not supported.
|
