Re: MERGE ... RETURNING - Mailing list pgsql-hackers

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