I've been thinking about adding RETURNING support to MERGE in order to
let the user see what changed.
I considered allowing a separate RETURNING list at the end of each
action, but rapidly dismissed that idea. Firstly, it introduces
shift/reduce conflicts to the grammar. These can be resolved by making
the "AS" before column aliases non-optional, but that's pretty ugly,
and there may be a better way. More serious drawbacks are that this
syntax is much more cumbersome for the end user, having to repeat the
RETURNING clause several times, and the implementation is likely to be
pretty complex, so I didn't pursue it.
A much simpler approach is to just have a single RETURNING list at the
end of the command. That's much easier to implement, and easier for
the end user. The main drawback is that it's impossible for the user
to work out from the values returned which action was actually taken,
and I think that's a pretty essential piece of information (at least
it seems pretty limiting to me, not being able to work that out).
So playing around with it (and inspired by the WITH ORDINALITY syntax
for SRFs), I had the idea of allowing "WITH WHEN CLAUSE" at the end of
the returning list, which adds an integer column to the list, whose
value is set to the index of the when clause executed, as in the
attached very rough patch.
So, quoting an example from the tests, this allows things like:
WITH t AS (
MERGE INTO sq_target t USING v ON tid = sid
WHEN MATCHED AND tid > 2 THEN UPDATE SET balance = t.balance + delta
WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid)
WHEN MATCHED AND tid < 2 THEN DELETE
RETURNING t.* WITH WHEN CLAUSE
)
SELECT CASE when_clause
WHEN 1 THEN 'UPDATE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'DELETE'
END, *
FROM t;
case | tid | balance | when_clause
--------+-----+---------+-------------
INSERT | -1 | -11 | 2
DELETE | 1 | 100 | 3
(2 rows)
1 row is returned for each merge action executed (other than DO
NOTHING actions), and as usual, the values represent old target values
for DELETE actions, and new target values for INSERT/UPDATE actions.
It's also possible to return the source values, and a bare "*" in the
returning list expands to all the source columns, followed by all the
target columns.
The name of the added column, if included, can be changed by
specifying "WITH WHEN CLAUSE [AS] col_alias". I chose the syntax "WHEN
CLAUSE" and "when_clause" as the default column name because those
match the existing terminology used in the docs.
Anyway, this feels like a good point to stop playing around and get
feedback on whether this seems useful, or if anyone has other ideas.
Regards,
Dean
