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.
示例:
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
注意该函数只能用于RETURNING
列表中,且仅限于MERGE
命令。
在查询的其他部分使用该函数将导致错误。