Horton

Re: MERGE ... RETURNING

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Gurjeet Singh <gurjeet@singh.im>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>, Vik Fearing <vik@postgresfriends.org>, Isaac Morland <isaac.morland@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-07-06T10:39:30Z
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 2023-Jul-05, Gurjeet Singh wrote:

> +BEGIN;
> +COPY (
> +    MERGE INTO sq_target t
> +    USING v
> +    ON tid = sid
> +    WHEN MATCHED AND tid > 2 THEN
> +        UPDATE SET balance = t.balance + delta
> +    WHEN NOT MATCHED THEN
> +        INSERT (balance, tid) VALUES (balance + delta, sid)
> +    WHEN MATCHED AND tid < 2 THEN
> +        DELETE
> +    RETURNING pg_merge_action(), t.*
> +) TO stdout;
> +DELETE  1   100
> +ROLLBACK;
> 
> I expected the .out file to have captured the stdout. I'm gradually,
> and gladly, re-learning bits of the test infrastructure.
> 
> The DELETE command tag in the output does not feel appropriate for a
> COPY command that's using MERGE as the source of the data.

You misread this one :-)  The COPY output is there, the tag is not.  So
DELETE is the value from pg_merge_action(), and "1 100" correspond to
the columns in the the sq_target row that was deleted.  The command tag
is presumably MERGE 1.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/