Re: MERGE ... RETURNING

On Sun, 8 Jan 2023 at 20:09, Isaac Morland <isaac(dot)morland(at)gmail(dot)com> wrote:
>
> 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.
>

Hmm, perhaps that's something that can be added as well. Both use
cases seem useful.

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

I too have wished for the ability to do that with UPDATE ...
RETURNING, though I'm not sure how feasible it is.

I think it's something best considered separately though. I haven't
given any thought as to how to make it work, so there might be
technical difficulties. But if it could be made to work for UPDATE, it
shouldn't be much more effort to make it work for MERGE.

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

Not really. It's exactly the same as doing "SELECT * FROM src JOIN tgt
ON ...". That may lead to duplicate column names in the result, but
that's not necessarily a problem.

Regards,
Dean