Movatterモバイル変換

Sometimes it is useful to obtain data from modified rows while they are being manipulated. TheINSERT,UPDATE,DELETE, andMERGE commands all have an optionalRETURNING clause that supports this. Use ofRETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.

The allowed contents of aRETURNING clause are the same as aSELECT command's output list (seeSection 7.3). It can contain column names of the command's target table, or value expressions using those columns. A common shorthand isRETURNING *, which selects all columns of the target table in order.

In anINSERT, the default data available toRETURNING is the row as it was inserted. This is not so useful in trivial inserts, since it would just repeat the data provided by the client. But it can be very handy when relying on computed default values. For example, when using aserial column to provide unique identifiers,RETURNING can return the ID assigned to a new row:

CREATE TABLE users (firstname text, lastname text, id serial primary key);INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

TheRETURNING clause is also very useful withINSERT ... SELECT.

In anUPDATE, the default data available toRETURNING is the new content of the modified row. For example:

UPDATE products SET price = price * 1.10  WHERE price <= 99.99  RETURNING name, price AS new_price;

In aDELETE, the default data available toRETURNING is the content of the deleted row. For example:

DELETE FROM products  WHERE obsoletion_date = 'today'  RETURNING *;

In aMERGE, the default data available toRETURNING is the content of the source row plus the content of the inserted, updated, or deleted target row. Since it is quite common for the source and target to have many of the same columns, specifyingRETURNING * can lead to a lot of duplicated columns, so it is often more useful to qualify it so as to return just the source or target row. For example:

MERGE INTO products p USING new_products n ON p.product_no = n.product_no  WHEN NOT MATCHED THEN INSERT VALUES (n.product_no, n.name, n.price)  WHEN MATCHED THEN UPDATE SET name = n.name, price = n.price  RETURNING p.*;

In each of these commands, it is also possible to explicitly return the old and new content of the modified row. For example:

UPDATE products SET price = price * 1.10  WHERE price <= 99.99  RETURNING name, old.price AS old_price, new.price AS new_price,            new.price - old.price AS price_change;

In this example, writingnew.price is the same as just writingprice, but it makes the meaning clearer.

This syntax for returning old and new values is available inINSERT,UPDATE,DELETE, andMERGE commands, but typically old values will beNULL for anINSERT, and new values will beNULL for aDELETE. However, there are situations where it can still be useful for those commands. For example, in anINSERT with anON CONFLICT DO UPDATE clause, the old values will be non-NULL for conflicting rows. Similarly, if aDELETE is turned into anUPDATE by arewrite rule, the new values may be non-NULL.

If there are triggers (Chapter 37) on the target table, the data available toRETURNING is the row as modified by the triggers. Thus, inspecting columns computed by triggers is another common use-case forRETURNING.