Início / dba / Perguntas / 308436
Accepted
cis
Asked: 2022-03-09 06:02:25 +0800 CST

Retornando na consulta se uma coluna foi alterada pela consulta atual ou não

  • 772

Eu tenho uma consulta como

UPDATE my_table 
  SET my_value = 
    CASE WHEN random() > 0.5 THEN my_value * 2 ELSE my_value END
RETURNING *;

Agora, dentro da RETURNINGdeclaração eu gostaria de ter um booleano indicando se my_valuefoi alterado pela consulta atual ou não.

Vamos supor que não posso passar o valor anterior de my_valuecomo parâmetro para a consulta.

Então, existe uma maneira de obter algo como uma lista de colunas que possuem valores diferentes após o UPDATE? Ou obter os valores no estado antes UPDATEem RETURNING?

No meu exemplo, eu poderia, claro, colocar o resultado de random()em um CTE como

WITH random_cte AS (
    SELECT random() AS my_random
)
UPDATE my_table 
  SET my_value = 
    CASE WHEN my_random > 0.5 THEN my_value * 2 ELSE my_value END
FROM random_cte
RETURNING *, my_random > 0.5 AS value_changed;

Mas isso aumentaria um pouco a consulta. Então eu estou querendo saber se eu poderia fazer isso de uma forma mais elegante?

postgresql update

1 respostas

  1. Best Answer

    As duas consultas não são equivalentes . A primeira consulta avaliaria a VOLATILEfunção random()para cada linha, enquanto a segunda a avaliaria uma vez no CTE, portanto, my_randomé a mesma para todas as linhas.

    E não, o CTE não será embutido. O manual:

    No entanto, se uma WITHconsulta não for recursiva e sem efeitos colaterais (ou seja, não SELECT contém funções voláteis ), ela poderá ser dobrada na consulta pai

    Minha ênfase em negrito.

    Mas isso provavelmente é apenas um acidente na construção do seu teste.

    Qualquer uma das consultas atualiza todas as linhas, mesmo que nada mude.

    Para obter seus valores value_changedde forma confiável, compare os valores pré UPDATEe pós UPDATE:

    UPDATE my_table t
SET    my_value = CASE WHEN random() > 0.5 THEN t.my_value * 2 ELSE t.my_value END
FROM  (SELECT id, my_value FROM my_table) pre
WHERE  t.id = pre.id
RETURNING t.*, t.my_value IS DISTINCT FROM pre.my_value AS value_changed;

    idsendo o PK ou qualquer outra (combinação de) coluna(s) única(s) não nula(s).

    random() > 0.5pode ser truee my_valueainda inalterado. Pense em NULLou 0.

    Há uma possível condição de corrida sob carga de gravação simultânea . Ver:

    Se o seu caso de uso é realmente tão simples (apenas uma única coluna a ser atualizada), você prefere suprimir as atualizações vazias para começar. Ver:

    • 2

relate perguntas