Re: MERGE ... RETURNING - Mailing list pgsql-hackers
| From | Isaac Morland |
|---|---|
| Subject | Re: MERGE ... RETURNING |
| Date | |
| Msg-id | CAMsGm5fLhasqG6Xfjh8eZ2NrCsHGzPF5kEAcB5Wpn1+bNf5sgQ@mail.gmail.com Whole thread Raw |
| In response to | MERGE ... RETURNING (Dean Rasheed <dean.a.rasheed@gmail.com>) |
| Responses |
Re: MERGE ... RETURNING
|
| List | pgsql-hackers |
On Sun, 8 Jan 2023 at 07:28, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
So playing around with it (and inspired by the WITH ORDINALITY syntax
for SRFs), I had the idea of allowing "WITH WHEN CLAUSE" at the end of
the returning list, which adds an integer column to the list, whose
value is set to the index of the when clause executed, as in the
attached very rough patch.
Would it be useful to have just the action? Perhaps "WITH ACTION"? My idea is that this would return an enum of INSERT, UPDATE, DELETE (so is "action" the right word?). It seems to me in many situations I would be more likely to care about which of these 3 happened rather than the exact clause that applied. This isn't necessarily meant to be instead of your suggestion because I can imagine wanting to know the exact clause, just an alternative that might suffice in many situations. Using it would also avoid problems arising from editing the query in a way which changes the numbers of the clauses.
So, quoting an example from the tests, this allows things like:
WITH t AS (
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 t.* WITH WHEN CLAUSE
)
SELECT CASE when_clause
WHEN 1 THEN 'UPDATE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'DELETE'
END, *
FROM t;
case | tid | balance | when_clause
--------+-----+---------+-------------
INSERT | -1 | -11 | 2
DELETE | 1 | 100 | 3
(2 rows)
1 row is returned for each merge action executed (other than DO
NOTHING actions), and as usual, the values represent old target values
for DELETE actions, and new target values for INSERT/UPDATE actions.
Would it be feasible to allow specifying old.column or new.column? These would always be NULL for INSERT and DELETE respectively but more useful with UPDATE. Actually I've been meaning to ask this question about UPDATE … RETURNING.
Actually, even with DELETE/INSERT, I can imagine wanting, for example, to get only the new values associated with INSERT or UPDATE and not the values removed by a DELETE. So I can imagine specifying new.column to get NULLs for any row that was deleted but still get the new values for other rows.
It's also possible to return the source values, and a bare "*" in the
returning list expands to all the source columns, followed by all the
target columns.
Does this lead to a problem in the event there are same-named columns between source and target?
The name of the added column, if included, can be changed by
specifying "WITH WHEN CLAUSE [AS] col_alias". I chose the syntax "WHEN
CLAUSE" and "when_clause" as the default column name because those
match the existing terminology used in the docs.
pgsql-hackers by date: