JFIF$        dd7 

Viewing File: /usr/share/doc/firebird/sql.extensions/README.returning

----------------
RETURNING clause
----------------

  Function:
    Allow to return the column values actually stored in the table as a result of the "INSERT",
    "UPDATE OR INSERT", UPDATE, DELETE and MERGE statements.
    The most common usage is to retrieve the value of the primary key generated inside a BEFORE-trigger.

  Authors:
    Dmitry Yemanov <yemanov@yandex.ru>
    Adriano dos Santos Fernandes <adrianosf@uol.com.br>

  Syntax rules:
    INSERT INTO ... VALUES (...) [RETURNING <returning_list> [INTO <variable_list>]]
    INSERT INTO ... SELECT ... [RETURNING <returning_list> [INTO <variable_list>]]
    UPDATE OR INSERT INTO ... VALUES (...) ... [RETURNING <returning_list> [INTO <variable_list>]]
    UPDATE ... [RETURNING <returning_list> [INTO <variable_list>]]
    DELETE FROM ... [RETURNING <returning_list> [INTO <variable_list>]]
    MERGE INTO ... [RETURNING <returning_list> [INTO <variable_list>]]

    <returning_list> ::= <expr> [[AS] <alias>] {, <expr> [[AS] <alias>]}...

  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;
    3. DELETE FROM T1 WHERE F1 = 1 RETURNING F2;
    4. UPDATE T1 SET F2 = F2 * 10 RETURNING OLD.F2, NEW.F2;
    5. UPDATE T1 SET F2 = F2 * 10 RETURNING OLD.F2 OLD_F2, NEW.F2 AS NEW_F2;
    6. UPDATE T1 SET F2 = F2 * 10 WHERE CURRENT OF C RETURNING NEW.F2;

  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. OLD and NEW could be used in RETURNING clause of UPDATE, INSERT OR UPDATE and MERGE statements.
    6. In UPDATE and INSERT OR UPDATE statements, unqualified or qualified by table name/alias
       fields are resolved to NEW.
    7. In MERGE WHEN MATCHED UPDATE and MERGE WHEN NOT MATCHED statements, unqualified or qualified
       by table name/alias fields are resolved to NEW. In MERGE WHEN MATCHED DELETE they are
       resolved to OLD.
    8. Since v4 it's possible to use * and alias.*, as well OLD.* and NEW.* where applicable.

  Limitations:
    1. The modify statement (INSERT, UPDATE, DELETE, MERGE) should operate in no more than one record
       (i.e. should be singleton).
    2. The statement always return one row in DSQL, even if no record is inserted/updated/deleted.
       This may be changed in the future (i.e. return empty resultset).
Back to Directory  nL+D550H?Mx ,D"v]qv;6*Zqn)ZP0!1 A "#a$2Qr D8 a Ri[f\mIykIw0cuFcRı?lO7к_f˓[C$殷WF<_W ԣsKcëIzyQy/_LKℂ;C",pFA:/]=H  ~,ls/9ć:[=/#f;)x{ٛEQ )~ =𘙲r*2~ a _V=' kumFD}KYYC)({ *g&f`툪ry`=^cJ.I](*`wq1dđ#̩͑0;H]u搂@:~וKL Nsh}OIR*8:2 !lDJVo(3=M(zȰ+i*NAr6KnSl)!JJӁ* %݉?|D}d5:eP0R;{$X'xF@.ÊB {,WJuQɲRI;9QE琯62fT.DUJ;*cP A\ILNj!J۱+O\͔]ޒS߼Jȧc%ANolՎprULZԛerE2=XDXgVQeӓk yP7U*omQIs,K`)6\G3t?pgjrmۛجwluGtfh9uyP0D;Uڽ"OXlif$)&|ML0Zrm1[HXPlPR0'G=i2N+0e2]]9VTPO׮7h(F*癈'=QVZDF,d߬~TX G[`le69CR(!S2!P <0x<!1AQ "Raq02Br#SCTb ?Ζ"]mH5WR7k.ۛ!}Q~+yԏz|@T20S~Kek *zFf^2X*(@8r?CIuI|֓>^ExLgNUY+{.RѪ τV׸YTD I62'8Y27'\TP.6d&˦@Vqi|8-OΕ]ʔ U=TL8=;6c| !qfF3aů&~$l}'NWUs$Uk^SV:U# 6w++s&r+nڐ{@29 gL u"TÙM=6(^"7r}=6YݾlCuhquympǦ GjhsǜNlɻ}o7#S6aw4!OSrD57%|?x>L |/nD6?/8w#[)L7+6〼T ATg!%5MmZ/c-{1_Je"|^$'O&ޱմTrb$w)R$& N1EtdU3Uȉ1pM"N*(DNyd96.(jQ)X 5cQɎMyW?Q*!R>6=7)Xj5`J]e8%t!+'!1Q5 !1 AQaqё#2"0BRb?Gt^## .llQT $v,,m㵜5ubV =sY+@d{N! dnO<.-B;_wJt6;QJd.Qc%p{ 1,sNDdFHI0ГoXшe黅XۢF:)[FGXƹ/w_cMeD,ʡcc.WDtA$j@:) -# u c1<@ۗ9F)KJ-hpP]_x[qBlbpʖw q"LFGdƶ*s+ډ_Zc"?%t[IP 6J]#=ɺVvvCGsGh1 >)6|ey?Lӣm,4GWUi`]uJVoVDG< SB6ϏQ@ TiUlyOU0kfV~~}SZ@*WUUi##; s/[=!7}"WN]'(L! ~y5g9T̅JkbM' +s:S +B)v@Mj e Cf jE 0Y\QnzG1д~Wo{T9?`Rmyhsy3!HAD]mc1~2LSu7xT;j$`}4->L#vzŏILS ֭T{rjGKC;bpU=-`BsK.SFw4Mq]ZdHS0)tLg