Re: MERGE ... RETURNING

On Mon, 9 Jan 2023 at 17:44, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>
> On Mon, 9 Jan 2023 at 16:23, Vik Fearing <vik(at)postgresfriends(dot)org> wrote:
> >
> > Bikeshedding here. Instead of Yet Another WITH Clause, could we perhaps
> > make a MERGING() function analogous to the GROUPING() function that goes
> > with grouping sets?
> >
> > MERGE ...
> > RETURNING *, MERGING('clause'), MERGING('action');
> >
>
> Hmm, possibly, but I think that would complicate the implementation quite a bit.
>
> GROUPING() is not really a function (in the sense that there is no
> pg_proc entry for it, you can't do "\df grouping", and it isn't
> executed with its arguments like a normal function). Rather, it
> requires special-case handling in the parser, through to the executor,
> and I think MERGING() would be similar.
>
> Also, it masks any user function with the same name, and would
> probably require MERGING to be some level of reserved keyword.
>

I thought about this some more, and I think functions do make more
sense here, rather than inventing a special WITH syntax. However,
rather than using a special MERGING() function like GROUPING(), which
isn't really a function at all, I think it's better (and much simpler
to implement) to have a pair of normal functions (one returning int,
and one text).

The example from the tests shows the sort of thing this allows:

MERGE INTO sq_target t USING sq_source s 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 pg_merge_when_clause() AS when_clause,
pg_merge_action() AS merge_action,
t.*,
CASE pg_merge_action()
WHEN 'INSERT' THEN 'Inserted '||t
WHEN 'UPDATE' THEN 'Added '||delta||' to balance'
WHEN 'DELETE' THEN 'Removed '||t
END AS description;

when_clause | merge_action | tid | balance | description
-------------+--------------+-----+---------+---------------------
3 | DELETE | 1 | 100 | Removed (1,100)
1 | UPDATE | 2 | 220 | Added 20 to balance
2 | INSERT | 4 | 40 | Inserted (4,40)
(3 rows)

I think this is easier to use than the WITH syntax, and more flexible,
since the new functions can be used anywhere in the RETURNING list,
including in expressions.

There is one limitation though. Due to the way these functions need
access to the originating query, they need to appear directly in
MERGE's RETURNING list, not in subqueries, plpgsql function bodies, or
anything else that amounts to a different query. Maybe there's a way
round that, but it looks tricky. In practice though, it's easy to work
around, if necessary (e.g., by wrapping the MERGE in a CTE).

Regards,
Dean