Horton

Re: MERGE ... RETURNING

Dean Rasheed <dean.a.rasheed@gmail.com>

View thread
From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: jian he <jian.universality@gmail.com>
Cc: walther@technowledgy.de, Jeff Davis <pgsql@j-davis.com>, Vik Fearing <vik@postgresfriends.org>, Gurjeet Singh <gurjeet@singh.im>, Isaac Morland <isaac.morland@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: 2024-03-15T11:20:10Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add RETURNING support to MERGE.

  2. doc: Improve a couple of places in the MERGE docs.

  3. doc: improve description of privileges for MERGE and update glossary.

  4. Fix RLS policy usage in MERGE.

  5. Fix leak of LLVM "fatal-on-oom" section counter.

  6. Implement outer-level aggregates to conform to the SQL spec, with

On Fri, 15 Mar 2024 at 11:06, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> Updated patch attached.
>

I have gone over this patch again in detail, and I believe that the
code is in good shape. All review comments have been addressed, and
the only thing remaining is the syntax question.

To recap, this adds support for a single RETURNING list at the end of
a MERGE command, and a special MERGE_ACTION() function that may be
used in the RETURNING list to return the action command string
('INSERT', 'UPDATE', or 'DELETE') that was executed.

Looking for similar precedents in other databases, SQL Server uses a
slightly different (non-standard) syntax for MERGE, and uses "OUTPUT"
instead of "RETURNING" to return rows. But it does allow "$action" in
the output list, which is functionally equivalent to MERGE_ACTION():

https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16#output_clause

In the future, we may choose to support the SQL standard syntax for
returning rows modified by INSERT, UPDATE, DELETE, and MERGE commands,
but I don't think that this patch needs to do that.

What this patch does is to make MERGE more consistent with INSERT,
UPDATE, and DELETE, by allowing RETURNING. And if the patch to add
support for returning OLD/NEW values [1] makes it in too, it will be
more powerful than the SQL standard syntax, since it will allow both
old and new values to be returned at the same time, in arbitrary
expressions.

So barring any further objections, I'd like to go ahead and get this
patch committed.

Regards,
Dean

[1] https://www.postgresql.org/message-id/flat/CAEZATCWx0J0-v=Qjc6gXzR=KtsdvAE7Ow=D=mu50AgOe+pvisQ@mail.gmail.com