Horton

Re: MERGE ... RETURNING

Wolfgang Walther <walther@technowledgy.de>

View thread
From: walther@technowledgy.de
To: Jeff Davis <pgsql@j-davis.com>, Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Vik Fearing <vik@postgresfriends.org>, Gurjeet Singh <gurjeet@singh.im>, Isaac Morland <isaac.morland@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, jian he <jian.universality@gmail.com>
Date: 2024-03-08T08:41:53Z
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

Jeff Davis:
> 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.

It seems to me that all of this is only a problem, because there is only
one RETURNING clause.

Dean Rasheed wrote in the very first post to this thread:
> I considered allowing a separate RETURNING list at the end of each
> action, but rapidly dismissed that idea. Firstly, it introduces
> shift/reduce conflicts to the grammar. These can be resolved by making
> the "AS" before column aliases non-optional, but that's pretty ugly,
> and there may be a better way. More serious drawbacks are that this
> syntax is much more cumbersome for the end user, having to repeat the
> RETURNING clause several times, and the implementation is likely to be
> pretty complex, so I didn't pursue it.

I can't judge the grammar and complexity issues, but as a potential user
it seems to me to be less complex to have multiple RETURNING clauses, 
where I could inject my own constants about the specific actions, than 
to have to deal with any of the suggested functions / clauses. More 
repetitive, yes - but not more complex.

More importantly, I could add RETURNING to only some of the actions and 
not always all at the same time - which seems pretty useful to me.

Best,

Wolfgang