Can we get some input on whether the current MERGE ... RETURNING patch is the right approach from a language standpoint?
We've gone through a lot of iterations -- thank you Dean, for implementing so many variations. 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. Granted, DELETE in a MERGE may be a less common case. But given that we also have INSERT ... ON CONFLICT, MERGE commands are more likely to be the complicated cases where distinguishing the action or clause number is important. But linguistically it's not clear where the action or clause number should come from. The clauses don't have assigned numbers, and even if they did, linguistically it's not clear how to refer to the clause number in a language like SQL. Would it be a special identifier, a function, a special function, or be a column in a special table reference? Or, do we just have one RETURNING-clause per WHEN-clause, and let the user use a literal of their choice in the RETURNING clause? 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. Is that a reasonable thing to do? Another related topic came up, which is that the RETURNING clause (for UPDATE as well as MERGE) should probably accept some kind of alias like NEW/OLD or BEFORE/AFTER to address the version of the row that you want. That doesn't eliminate the need for the MERGING function, but it's good to think about how that might fit in with whatever we do here. Regards, Jeff Davis