Re: MERGE ... RETURNING

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.

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.

(b) It could be a FuncExpr that is passed the information out-of-band
(i.e. not as an argument) and would fail at runtime if called in the
wrong context.

(c) It could be a Var (or perhaps a Param?), but to which table would
it refer? The source or the target table could be used, but we would
also need a special table reference to represent additional context
(WHEN clause number, action, or "after" version of the tuple).

Dean's v11 patch had kind of a combination of (a) and (b). It's raises
an error at parse analysis time like (a), but without any grammar
changes or new expr kind because it's a function. I must admit that
might be a very reasonable compromise and I certainly won't reject it
without a clearly better alternative. It does feel like a hack though
in the sense that it's hard-coding function OIDs into the parse
analysis and I'm not sure that's a great thing to do. I wonder if it
would be worth thinking about a way to make it generic by really making
it into a different kind of function with pg_proc support? That feels
like over-engineering, and I hate to generalize from a single use case,
but it might be a good thought exercise.

The cleanest from a SQL perspective (in my opinion) would be something
more like (c), because the merge action and WHEN clause number would be
passed in tuple data. It also would be good precedent for something
like BEFORE/AFTER aliases, which could be useful for UPDATE actions.
But given the implementation complexities brought up earlier (I haven't
looked into the details, but others have), that might be over-
engineering.

Regards,
	Jeff Davis