Re: MERGE ... RETURNING

On Sun, 8 Jan 2023 at 07:28, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:

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.
>

Would it be useful to have just the action? Perhaps "WITH ACTION"? My idea
is that this would return an enum of INSERT, UPDATE, DELETE (so is "action"
the right word?). It seems to me in many situations I would be more likely
to care about which of these 3 happened rather than the exact clause that
applied. This isn't necessarily meant to be instead of your suggestion
because I can imagine wanting to know the exact clause, just an alternative
that might suffice in many situations. Using it would also avoid problems
arising from editing the query in a way which changes the numbers of the
clauses.

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.
>

Would it be feasible to allow specifying old.column or new.column? These
would always be NULL for INSERT and DELETE respectively but more useful
with UPDATE. Actually I've been meaning to ask this question about UPDATE …
RETURNING.

Actually, even with DELETE/INSERT, I can imagine wanting, for example, to
get only the new values associated with INSERT or UPDATE and not the values
removed by a DELETE. So I can imagine specifying new.column to get NULLs
for any row that was deleted but still get the new values for other rows.

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.
>

Does this lead to a problem in the event there are same-named columns
between source and target?

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.
>