Re: MERGE ... RETURNING

On 29.02.24 20:49, Jeff Davis wrote:
> To summarize, most of the problem has been in retrieving the action
> (INSERT/UPDATE/DELETE) taken or the WHEN-clause number applied to a
> particular matched row. The reason this is important is because the row
> returned is the old row for a DELETE action, and the new row for an
> INSERT or UPDATE action. Without a way to distinguish the particular
> action, the RETURNING clause returns a mixture of old and new rows,
> which would be hard to use sensibly.

For comparison with standard SQL (see <data change delta table>):

For an INSERT you could write

SELECT whatever FROM NEW TABLE (INSERT statement here)

or for an DELETE

SELECT whatever FROM OLD TABLE (DELETE statement here)

And for an UPDATE could can pick either OLD or NEW.

(There is also FINAL, which appears to be valid in cases where NEW is
valid. Here is an explanation:
<https://www.ibm.com/docs/en/db2oc?topic=statement-result-sets-from-sql-data-changes>)

For a MERGE statement, whether you can specify OLD or NEW (or FINAL)
depends on what actions appear in the MERGE statement.

So if we were to translate that to our syntax, it might be something like

MERGE ... RETURNING OLD *

or

MERGE ... RETURNING NEW *

This wouldn't give you the ability to return both old and new. (Is that
useful?) But maybe you could also do something like

MERGE ... RETURNING OLD 'old'::text, * RETURNING NEW 'new'::text, *

(I mean here you could insert your own constants into the returning lists.)

> The current implementation uses a special function MERGING (a
> grammatical construct without an OID that parses into a new MergingFunc
> expr), which takes keywords ACTION or CLAUSE_NUMBER in the argument
> positions. That's not totally unprecedented in SQL -- the XML and JSON
> functions are kind of similar. But it's different in the sense that
> MERGING is also context-sensitive: grammatically, it fits pretty much
> anywhere a function fits, but then gets rejected at parse analysis time
> (or perhaps even execution time?) if it's not called from the right
> place.

An analogy here might be that MATCH_RECOGNIZE (row-pattern recognition)
has a magic function MATCH_NUMBER() that can be used inside that clause.
So a similar zero-argument magic function might make sense. I don't
like the MERGING(ACTION) syntax, but something like MERGE_ACTION() might
make sense. (This is just in terms of what kind of syntax might be
palatable. Depending on where the syntax of the overall clause ends up,
we might not need it (see above).)