| 1 |
---------------- |
| 2 |
RETURNING clause |
| 3 |
---------------- |
| 4 |
|
| 5 |
Function: |
| 6 |
Allow to return the column values actually stored in the table as a result of the "INSERT", |
| 7 |
"UPDATE OR INSERT", UPDATE, DELETE and MERGE statements. |
| 8 |
The most common usage is to retrieve the value of the primary key generated inside a BEFORE-trigger. |
| 9 |
|
| 10 |
Authors: |
| 11 |
Dmitry Yemanov <[email protected]> |
| 12 |
Adriano dos Santos Fernandes <[email protected]> |
| 13 |
|
| 14 |
Syntax rules: |
| 15 |
INSERT INTO ... VALUES (...) [RETURNING <returning_list> [INTO <variable_list>]] |
| 16 |
INSERT INTO ... SELECT ... [RETURNING <returning_list> [INTO <variable_list>]] |
| 17 |
UPDATE OR INSERT INTO ... VALUES (...) ... [RETURNING <returning_list> [INTO <variable_list>]] |
| 18 |
UPDATE ... [RETURNING <returning_list> [INTO <variable_list>]] |
| 19 |
DELETE FROM ... [RETURNING <returning_list> [INTO <variable_list>]] |
| 20 |
MERGE INTO ... [RETURNING <returning_list> [INTO <variable_list>]] |
| 21 |
|
| 22 |
<returning_list> ::= <expr> [[AS] <alias>] {, <expr> [[AS] <alias>]}... |
| 23 |
|
| 24 |
Scope: |
| 25 |
DSQL, PSQL |
| 26 |
|
| 27 |
Example(s): |
| 28 |
1. INSERT INTO T1 (F1, F2) |
| 29 |
VALUES (:F1, :F2) |
| 30 |
RETURNING F1, F2 INTO :V1, :V2; |
| 31 |
2. INSERT INTO T2 (F1, F2) |
| 32 |
VALUES (1, 2) |
| 33 |
RETURNING ID INTO :PK; |
| 34 |
3. DELETE FROM T1 WHERE F1 = 1 RETURNING F2; |
| 35 |
4. UPDATE T1 SET F2 = F2 * 10 RETURNING OLD.F2, NEW.F2; |
| 36 |
5. UPDATE T1 SET F2 = F2 * 10 RETURNING OLD.F2 OLD_F2, NEW.F2 AS NEW_F2; |
| 37 |
6. UPDATE T1 SET F2 = F2 * 10 WHERE CURRENT OF C RETURNING NEW.F2; |
| 38 |
|
| 39 |
Note(s): |
| 40 |
1. The INTO part (i.e. the variable list) is allowed in PSQL only (to assign local variables) |
| 41 |
and rejected in DSQL. |
| 42 |
2. In DSQL, values are being returned within the same protocol roundtrip as the INSERT itself |
| 43 |
is executed. |
| 44 |
3. If the RETURNING clause is present, then the statement is described as |
| 45 |
isc_info_sql_stmt_exec_procedure by the API (instead of isc_info_sql_stmt_insert), |
| 46 |
so the existing connectivity drivers should support this feature automagically. |
| 47 |
4. Any explicit record change (update or delete) performed by AFTER-triggers is ignored by |
| 48 |
the RETURNING clause. |
| 49 |
5. OLD and NEW could be used in RETURNING clause of UPDATE, INSERT OR UPDATE and MERGE statements. |
| 50 |
6. In UPDATE and INSERT OR UPDATE statements, unqualified or qualified by table name/alias |
| 51 |
fields are resolved to NEW. |
| 52 |
7. In MERGE WHEN MATCHED UPDATE and MERGE WHEN NOT MATCHED statements, unqualified or qualified |
| 53 |
by table name/alias fields are resolved to NEW. In MERGE WHEN MATCHED DELETE they are |
| 54 |
resolved to OLD. |
| 55 |
|
| 56 |
Limitations: |
| 57 |
1. The modify statement (INSERT, UPDATE, DELETE, MERGE) should operate in no more than one record |
| 58 |
(i.e. should be singleton). |
| 59 |
2. The statement always return one row in DSQL, even if no record is inserted/updated/deleted. |
| 60 |
This may be changed in the future (i.e. return empty resultset). |