UPSERT 函式中的複合類型問題

我在 PostgreSQL 9.1 中有一個名為fun_test. 它有一個複合類型作為輸入參數，當我呼叫它時我不斷收到轉換錯誤。

CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.testobj)
 RETURNS boolean AS
$BODY$
DECLARE 
tmp_code smallint;
cur_member refcursor;
BEGIN
-- Check if the member exists first
OPEN cur_member FOR 
EXECUTE 'SELECT testkey FROM netcen.test WHERE testkey=' || myobj.testkey ;
FETCH cur_member INTO tmp_code;
CLOSE cur_member;
CASE tmp_code
   WHEN COALESCE(tmp_code,0)=0 THEN
   -- Record not found INSERT a new record
   -- will skip user defined validation for now
   insert into netcen.test values(myobj.testkey,
   myobj.tes,
   myobj.testname);

   ELSE
   -- Record found UPDATE the record
   update netcen.test set 
   test=myobj.test,
   testname=myobj.testname  WHERE testkey=myobj.testkey;

END CASE;
END;$BODY$
 LANGUAGE plpgsql;

下面是類型testobj

CREATE TYPE netcen.testobj AS
  (testkey smallint,
   tes text,
   testname text);

當我呼叫函式時：

SELECT netcen.fun_test('(3,khaendra@me.com,khaendra)':: netcen.testobj);

.. 我收到以下錯誤消息：

ERROR:  operator does not exist: smallint = boolean
LINE 1: SELECT "__Case__Variable_8__" IN (COALESCE(tmp_code,0)=0)
                                     ^
HINT:  No operator matches the given name and argument type(s).
      You might need to add explicit type casts.
QUERY:  SELECT "__Case__Variable_8__" IN (COALESCE(tmp_code,0)=0)
CONTEXT:  PL/pgSQL function "fun_test" line 11 at CASE

我應該在哪裡投？

表定義netcen.test：

CREATE TABLE netcen.test    (
 testkey smallint NOT NULL DEFAULT 0,
 tes netcen.dom_email_validation,
 testname text,
 CONSTRAINT key PRIMARY KEY (testkey)
)

---

@ Erwin，感謝您的連結。我閱讀並修改了我的函式，請仔細閱讀並告訴我它是否可以與同時呼叫相同函式的多個客戶端一起正常工作？

CREATE OR REPLACE FUNCTION netcen.fun_test_modified(myobj netcen.test)
 RETURNS boolean AS
$BODY$
DECLARE 
myoutput boolean :=false;
BEGIN
   update netcen.test set 
   tes=myobj.tes,
   testname=myobj.testname  WHERE testkey=myobj.testkey;
IF FOUND THEN
       myoutput:= TRUE;
       RETURN myoutput;
   END IF;
   BEGIN
       INSERT INTO netcen.test values(myobj.testkey,
   myobj.tes,
   myobj.testname);
   myoutput:= TRUE;
   EXCEPTION WHEN OTHERS THEN
       update netcen.test set 
   tes=myobj.tes,
   testname=myobj.testname  WHERE testkey=myobj.testkey;
   myoutput:= TRUE;
   END;
   RETURN myoutput;
END;
$BODY$ LANGUAGE plpgsql;

我已經取消了類型測試，只使用了表格test！我不知道這可以工作！

回答

錯誤發生在這裡：

CASE tmp_code
WHEN COALESCE(tmp_code,0)=0 THEN 

像這樣工作：

CASE WHEN COALESCE(tmp_code,0)=0 THEN

您以不兼容的方式混合了PL/pgSQLCASE的兩種不同語法變體（“簡單”與“搜尋”）。

還有另一個錯誤：

update netcen.test set 
test=myobj.test,
testname=myobj.testname  WHERE testkey=myobj.testkey;

你的意思是：

UPDATE test
SET    **tes** = myobj.**tes**
    , testname = myobj.testname
WHERE  testkey = myobj.testkey;

也沒有必要CREATE TYPE netcen.testobj ...。您可以使用表名netcen.test作為類型名。

你真的想“UPSERT”

在 Postgres 9.5 或更高版本中使用新的 UPSERT ( INSERT ... ON CONFLICT DO UPDATE)。喜歡：

• UPSERT 與 ON CONFLICT 使用 UPDATE 部分中源表中的值

---

對於舊版本，您可以模擬**UPSERT**. plpgsql中的簡單形式，沒有並發：

CREATE OR REPLACE FUNCTION fun_test(myobj testobj)
 RETURNS boolean
 LANGUAGE plpgsql AS
$func$
BEGIN    
  UPDATE test
  SET    tes = myobj.tes
       , testname = myobj.testname
  WHERE  testkey = myobj.testkey;

  IF FOUND THEN
     RETURN FALSE;
  ELSE
     INSERT INTO test SELECT (myobj).*;
     RETURN TRUE;
  END IF;
END
$func$;

可能只是帶有**數據修改 CTE**的普通 SQL ：

WITH my_row(testkey, tes, testname) AS (
   SELECT 1::smallint, 'khaendra@me.net', 'khaendra'
   )
, u AS (
   UPDATE test t
   SET    tes = m.tes
        , testname = m.testname
   FROM   my_row m
   WHERE  t.testkey = m.testkey
   RETURNING t.testkey
   )
INSERT INTO test (testkey, tes, testname)
SELECT * FROM my_row
WHERE  NOT EXISTS (SELECT FROM u);

使用這種形式（單個組合語句），可能的競爭條件的時間視窗*非常小。*如果並發仍然是一個問題（大量並發寫入負載），那麼……

您的功能已審核

如果函式返回，則該行已被插入或更新。唯一的另一種方式是EXCEPTION另一種方式。返回的值true只是噪音。true（返回forINSERT和falsefor可能更有趣UPDATE。）所以我簡化了：

CREATE OR REPLACE FUNCTION netcen.fun_test_modified(myobj netcen.test)
 RETURNS boolean
 LANGUAGE plpgsql AS
$func$
BEGIN
  UPDATE netcen.test
  SET    tes = myobj.tes
       , testname = myobj.testname
  WHERE  testkey = myobj.testkey;

  IF FOUND THEN
     RETURN true;
  END IF;

  BEGIN
     INSERT INTO netcen.test 
     SELECT (myobj).*;  -- simpler form, parenthesis needed.

  EXCEPTION WHEN unique_violation THEN   -- cleaner
     UPDATE netcen.test
     SET    tes = myobj.tes
          , testname = myobj.testname
     WHERE  testkey = myobj.testkey;
  END;
  RETURN true;
END
$func$;

關於 SO 的相關答案：

• 使用事務更新插入
• 函式中的 SELECT 或 INSERT 是否容易出現競爭條件？

Depesz 關於 UPSERT 的部落格文章。

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