互動式 INSERT/UPDATE 功能實現 UPSERT

我在 PostgreSQL 9.1 中有一個函式，我希望客戶端應用程序傳遞一個記錄對象，該函式將檢測它是插入還是更新並返回完成的操作，即“成功添加 1 條記錄”或“使用 PK 記錄”

$$ wherever $$已成功更新”或“發生錯誤，欄位 X 不能為空”或“出現任何錯誤”。 我想避免一個昂貴CURSOR甚至更昂貴的EXCEPTION條款。我還希望它針對速度進行優化。該函式應考慮並發，因為將有超過 50 個客戶端同時連接到數據庫，每個更新/插入（呼叫相同的函式）。這是我目前擁有的：

CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.test)
 RETURNS text AS
$BODY$
DECLARE 
myoutput text :='Nothing has occured';
BEGIN
   update netcen.test set 
   tes=myobj.tes,
   testname=myobj.testname  WHERE testkey=myobj.testkey;
IF FOUND THEN
       myoutput:= 'Record with PK[' || myobj.testkey || '] successfully updated';
       RETURN myoutput;
   END IF;
   BEGIN
       INSERT INTO netcen.test values(myobj.testkey,
   myobj.tes,
   myobj.testname);
   myoutput:= 'Record successfully added';
   EXCEPTION WHEN null_value_not_allowed THEN
       RAISE NOTICE 'something has just occured';
   myoutput:= 'A field was null';
   WHEN not_null_violation THEN
       RAISE NOTICE 'something has just occured';
   myoutput:= 'A field was null';
   WHEN unique_violation THEN
   RAISE NOTICE 'something has just occured';
   myoutput:= 'A field was null';
   END;
   RETURN myoutput;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;

我知道這個函式可以簡化為 SQL 語言的一個簡單且更短的可寫 CTE 函式。但是那些有競爭條件。而且我不知道如何擷取錯誤並將完成的操作作為輸出報告。

這個功能可以簡化或複雜，但可以解決我所有的問題嗎？

如果您還想擷取NULL違規行為，則還必須涵蓋 UPDATE，這使得該功能更加昂貴。

SET search_path = netcen;

CREATE OR REPLACE FUNCTION fun_test(myobj test, OUT myoutput text)
 RETURNS text AS
$func$
BEGIN
  UPDATE test
  SET    tes = myobj.tes,
         testname = myobj.testname
  WHERE  testkey = myobj.testkey;

  IF FOUND THEN
     myoutput := 'UPDATE successfull. testkey = ' || myobj.testkey;
     RETURN;
  END IF;

  INSERT INTO test
  SELECT (myobj).*;
  myoutput := 'INSERT successfull. testkey = ' || myobj.testkey;

EXCEPTION
WHEN null_value_not_allowed THEN
  RAISE NOTICE 'null_value_not_allowed occurred.';
  myoutput := 'A field was null.';

WHEN not_null_violation THEN
  RAISE NOTICE 'not_null_violation occurred.';
  myoutput:= 'A field was null.';

WHEN unique_violation THEN
  RAISE NOTICE 'unique_violation occurred.';
  myoutput:= 'Duplicate value.';
END
$func$ LANGUAGE plpgsql;

使用一個OUT參數並做了一些簡化和說明。

我只會讓正常的異常發生。人們不應該為 NOT NULL 列輸入 NULL 值。這樣，UPDATE 可以在 EXCEPTION 塊之外執行。

通讀您的程式碼，不清楚您要針對可能的競爭條件做什麼。人們擔心的標準比賽條件就在那裡。你只是提出一個通知，我相信，繼續。現在，如果您有多個唯一約束，您需要有某種方法來檢測它並引發異常。所以像：

CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.test, is_retry bool)   
RETURNS text AS $BODY$ 
DECLARE  myoutput text :='Nothing has occured'; 
BEGIN
   WITH my_update (tes, testname, testkey) AS (
        update netcen.test 
           SET tes=myobj.tes,
               testname=myobj.testname  
         WHERE testkey=myobj.testkey
         RETURNING tes, testname, testkey)
  INSERT INTO netcen.test (tes, testname, testkey)
  SELECT myobj.tes, myobj.testname, myobj.testkey
    FROM my_update WHERE testkey IS NULL; 

   EXCEPTION 
        WHEN null_value_not_allowed THEN
       RAISE NOTICE 'something has just occured';
      return 'A field was null';
        WHEN not_null_violation THEN
       RAISE NOTICE 'something has just occured';
      RETURN 'A field was null';
        WHEN unique_violation THEN
             IF is_retry THEN
                RAISE NOTICE 'actual unique conflict';
                return 'unique key conflict';
             ELSE
                return fun_test(my_obj, true); --retry
             END IF;
   END;
   RETURN 'Success';
END; 
$BODY$   LANGUAGE plpgsql VOLATILE   COST 100;

我還沒有完全測試過這個，所以它可能有錯別字等，但基本概念應該可以很好地工作並解決你關心的競爭條件（但目前仍然有）。

引用自：https://dba.stackexchange.com/questions/33389