PostgreSQL-更新失败时返回行内容 [英] PostgreSQL - Return the row content when UPDATE fails

问题描述

使用PostgreSQL更新行时（通过UPDATE语句），可以使用 RETURNING * 取回修改后的行内容。

When updating a row with PostgreSQL (through the UPDATE statement), this is possible to get back the modified row content using RETURNING *.

我想知道由于约束而导致更新失败时是否有任何方式获取未修改的行。例如，执行以下命令（使用 RETURNING * ）不会返回当前行值：

I would like to know if there is any way to get the unmodified row when the update fails because of constraints. For example, executing the following command (with RETURNING *) does not return the current row values:

UPDATE sentence
SET content = 'This is a sentence', language_id=834
WHERE id = '0538f24a-2046-4da6-933d-409aa7b7c597'
RETURNING *;

出现错误

ERROR:  duplicate key value violates unique constraint "sentence_language_id_content_key"
DETAIL:  Key (language_id, content)=(834, This is a sentence) already exists.

是否可以通过任何方式返回未修改的行内容或 RETURNING * 仅在实际发生数据修改而没有任何约束错误的情况下起作用？

Is there any way to return the unmodified row content or does RETURNING * only work when a data modification actually happened without any constraint error ?

推荐答案

一个可以继续使用更新的选项并且仍然返回冲突的行将是创建一个在执行更新之前执行此检查的函数。如果主键已经存在，它将返回冲突的行，如果不存在，则更新该行。此类函数的示例可能是：

One option to keep using update and still returning the conflicting rows would be to create a function that does this check before you perform the update. In case the the primary key already exists, it returns the conflicting row, and If not, the row gets updated. An example of such a function could be:

CREATE OR REPLACE FUNCTION my_func(cont TEXT, lang INT, idp TEXT) 
RETURNS SETOF sentence AS $$
DECLARE j RECORD;
BEGIN
  SELECT * FROM sentence WHERE content = cont AND 
                               language_id = lang INTO j;
  IF j IS NOT NULL THEN 
    RETURN NEXT j;
  ELSE
    RETURN QUERY UPDATE sentence SET content = cont, language_id= lang 
                 WHERE id = idp RETURNING *;
  END IF;
  EXCEPTION WHEN SQLSTATE '23505' THEN 
  RETURN QUERY SELECT * FROM sentence WHERE content = cont AND
                                            language_id= lang;
END$$ LANGUAGE plpgsql;

SELECT my_func('This is a sentence',834,'0538f24a-2046-4da6-933d-409aa7b7c597');

编辑：如果没有冲突，您仍然希望返回* ，只需在 UPDATE 语句中添加返回查询：

EDIT: If there is no conflict and you still want the RETURNING *, just add a RETURN QUERY to the UPDATE statement:

...    
RETURN QUERY 
UPDATE sentence SET content = cont, language_id= lang WHERE id = idp 
RETURNING *;
...

这篇关于PostgreSQL-更新失败时返回行内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！