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