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
