PostgreSQL includes one merge support function that may be used in the RETURNING list of a MERGE command to identify the action taken for each row; see Table 9.66.
Table 9.66. Merge Support Functions
Function
Description
Returns the merge action command executed for the current row. This will be 'INSERT', 'UPDATE', or 'DELETE'.
Example:
MERGE INTO products p
USING stock s ON p.product_id = s.product_id
WHEN MATCHED AND s.quantity > 0 THEN
UPDATE SET in_stock = true, quantity = s.quantity
WHEN MATCHED THEN
UPDATE SET in_stock = false, quantity = 0
WHEN NOT MATCHED THEN
INSERT (product_id, in_stock, quantity)
VALUES (s.product_id, true, s.quantity)
RETURNING merge_action(), p.*;
merge_action | product_id | in_stock | quantity
--------------+------------+----------+----------
UPDATE | 1001 | t | 50
UPDATE | 1002 | f | 0
INSERT | 1003 | t | 10
Note that this function can only be used in the RETURNING list of a MERGE command. It is an error to use it in any other part of a query.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4