MERGE ... RETURNING
От | Dean Rasheed |
---|---|
Тема | MERGE ... RETURNING |
Дата | |
Msg-id | CAEZATCWePEGQR5LBn-vD6SfeLZafzEm2Qy_L_Oky2=qw2w3Pzg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: MERGE ... RETURNING
(Isaac Morland <isaac.morland@gmail.com>)
Re: MERGE ... RETURNING (Jeff Davis <pgsql@j-davis.com>) |
Список | pgsql-hackers |
I've been thinking about adding RETURNING support to MERGE in order to let the user see what changed. 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. A much simpler approach is to just have a single RETURNING list at the end of the command. That's much easier to implement, and easier for the end user. The main drawback is that it's impossible for the user to work out from the values returned which action was actually taken, and I think that's a pretty essential piece of information (at least it seems pretty limiting to me, not being able to work that out). 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. 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. 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. 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. Anyway, this feels like a good point to stop playing around and get feedback on whether this seems useful, or if anyone has other ideas. Regards, Dean
Вложения
В списке pgsql-hackers по дате отправления: