Re: MERGE ... RETURNING

On 10/31/23 19:28, Jeff Davis wrote:
> On Tue, 2023-10-31 at 12:45 +0100, Vik Fearing wrote:
>> On 10/24/23 21:10, Jeff Davis wrote:
>>> Can we revisit the idea of a per-WHEN RETURNING clause?
>>
>> For the record, I dislike this idea.
>
> I agree that it makes things awkward, and if it creates grammatical
> problems as well, then it's not very appealing.
>
> There are only so many approaches though, so it would be helpful if you
> could say which approach you prefer.

This isn't as easy to answer for me as it seems because I care deeply
about respecting the standard. The standard does not have RETURNING at
all but instead has <data change delta table> and the results for that
for a MERGE statement are clearly defined.

On the other hand, we don't have that and we do have RETURNING so we
should improve upon that if we can.

One thing I don't like about either solution is that you cannot get at
both the old row versions and the new row versions at the same time. I
don't see how <data change delta table>s can be fixed to support that,
but RETURNING certainly can be with some spelling of OLD/NEW or
BEFORE/AFTER or whatever.

> Assuming we have one RETURNING clause at the end, then it creates the
> problem of how to communicate which WHEN clause a tuple came from,
> whether it's the old or the new version, and/or which action was
> performed on that tuple.
>
> How do we communicate any of those things? We need to get that
> information into the result table somehow, so it should probably be
> some kind of expression that can exist in the RETURNING clause. But
> what kind of expression?
>
> (a) It could be a totally new expression kind with a new keyword (or
> recycling some existing keywords for the same effect, or something that
> looks superficially like a function call but isn't) that's only valid
> in the RETURNING clause of a MERGE statement. If you use it in another
> expression (say the targetlist of a SELECT statement), then you'd get a
> failure at parse analysis time.

This would be my choice, the same as how the standard GROUPING()
"function" for grouping sets is implemented by GroupingFunc.

--
Vik Fearing