Re: MERGE ... RETURNING

On Mon, 17 Jul 2023 at 20:43, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
> > > Maybe instead of a function it could be a special table reference
> > > like:
> > >
> > > MERGE ... RETURNING MERGE.action, MERGE.action_number, id, val?
> > >
> The benefits are:
>
> 1. It is naturally constrained to the right context. It doesn't require
> global variables and the PG_TRY/PG_FINALLY, and can't be called in the
> wrong contexts (like SELECT).
>
> 2. More likely to be consistent with eventual support for NEW/OLD
> (actually BEFORE/AFTER for reasons the prior thread discussed).
>

Thinking about this some more, I think that the point about
constraining these functions to the right context is a reasonable one,
and earlier versions of this patch did that better, without needing
global variables or a PG_TRY/PG_FINALLY block.

Here is an updated patch that goes back to doing it that way. This is
more like the way that aggregate functions and GROUPING() work, in
that the parser constrains the location from which the functions can
be used, and at execution time, the functions rely on the relevant
context being passed via the FunctionCallInfo context.

It's still possible to use these functions in subqueries in the
RETURNING list, but attempting to use them anywhere else (like a
SELECT on its own) will raise an error at parse time. If they do
somehow get invoked in a non-MERGE context, they will elog an error
(again, just like aggregate functions), because that's a "shouldn't
happen" error.

This does nothing to be consistent with eventual support for
BEFORE/AFTER, but I think that's really an entirely separate thing,
and likely to work quite differently, internally.

From a user perspective, writing something like "BEFORE.id" is quite
natural, because it's clear that "id" is a column, and "BEFORE" is the
old state of the table. Writing something like "MERGE.action" seems a
lot more counter-intuitive, because "action" isn't a column of
anything (and if it was, I think this syntax would potentially cause
even more confusion).

So really, I think "MERGE.action" is an abuse of the syntax,
inconsistent with any other SQL syntax, and using functions is much
more natural, akin to GROUPING(), for example.

Regards,
Dean