begriffs open source - ai-pg/blob - full-docs/txt/dml-returning.txt

   1
   2 6.4. Returning Data from Modified Rows #
   3
   4    Sometimes it is useful to obtain data from modified rows while they are
   5    being manipulated. The INSERT, UPDATE, DELETE, and MERGE commands all
   6    have an optional RETURNING clause that supports this. Use of RETURNING
   7    avoids performing an extra database query to collect the data, and is
   8    especially valuable when it would otherwise be difficult to identify
   9    the modified rows reliably.
  10
  11    The allowed contents of a RETURNING clause are the same as a SELECT
  12    command's output list (see Section 7.3). It can contain column names of
  13    the command's target table, or value expressions using those columns. A
  14    common shorthand is RETURNING *, which selects all columns of the
  15    target table in order.
  16
  17    In an INSERT, the default data available to RETURNING is the row as it
  18    was inserted. This is not so useful in trivial inserts, since it would
  19    just repeat the data provided by the client. But it can be very handy
  20    when relying on computed default values. For example, when using a
  21    serial column to provide unique identifiers, RETURNING can return the
  22    ID assigned to a new row:
  23 CREATE TABLE users (firstname text, lastname text, id serial primary key);
  24
  25 INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
  26
  27    The RETURNING clause is also very useful with INSERT ... SELECT.
  28
  29    In an UPDATE, the default data available to RETURNING is the new
  30    content of the modified row. For example:
  31 UPDATE products SET price = price * 1.10
  32   WHERE price <= 99.99
  33   RETURNING name, price AS new_price;
  34
  35    In a DELETE, the default data available to RETURNING is the content of
  36    the deleted row. For example:
  37 DELETE FROM products
  38   WHERE obsoletion_date = 'today'
  39   RETURNING *;
  40
  41    In a MERGE, the default data available to RETURNING is the content of
  42    the source row plus the content of the inserted, updated, or deleted
  43    target row. Since it is quite common for the source and target to have
  44    many of the same columns, specifying RETURNING * can lead to a lot of
  45    duplicated columns, so it is often more useful to qualify it so as to
  46    return just the source or target row. For example:
  47 MERGE INTO products p USING new_products n ON p.product_no = n.product_no
  48   WHEN NOT MATCHED THEN INSERT VALUES (n.product_no, n.name, n.price)
  49   WHEN MATCHED THEN UPDATE SET name = n.name, price = n.price
  50   RETURNING p.*;
  51
  52    In each of these commands, it is also possible to explicitly return the
  53    old and new content of the modified row. For example:
  54 UPDATE products SET price = price * 1.10
  55   WHERE price <= 99.99
  56   RETURNING name, old.price AS old_price, new.price AS new_price,
  57             new.price - old.price AS price_change;
  58
  59    In this example, writing new.price is the same as just writing price,
  60    but it makes the meaning clearer.
  61
  62    This syntax for returning old and new values is available in INSERT,
  63    UPDATE, DELETE, and MERGE commands, but typically old values will be
  64    NULL for an INSERT, and new values will be NULL for a DELETE. However,
  65    there are situations where it can still be useful for those commands.
  66    For example, in an INSERT with an ON CONFLICT DO UPDATE clause, the old
  67    values will be non-NULL for conflicting rows. Similarly, if a DELETE is
  68    turned into an UPDATE by a rewrite rule, the new values may be
  69    non-NULL.
  70
  71    If there are triggers (Chapter 37) on the target table, the data
  72    available to RETURNING is the row as modified by the triggers. Thus,
  73    inspecting columns computed by triggers is another common use-case for
  74    RETURNING.