UPSERT 語句永遠不會完成，無限循環？

根據stackoverflow的一些答案，我寫了以下UPSERT語句，我認為它必須陷入無限循環。我在其中添加了一條語句以避免無限循環（因為在這種情況下，插入和更新語句可能不匹配，因此進入無限循環）：

CREATE FUNCTION upsert(sql_insert TEXT, sql_update TEXT) RETURNS VOID AS
$$
DECLARE
   i integer := 0;
BEGIN
   LOOP
       -- terminate if loop reaches 2 runs
       IF i >= 2 THEN
           RETURN;
       END  IF;
       BEGIN
           -- try update
           EXECUTE sql_update;
           -- terminate if update successful
           IF found THEN
               RETURN;
           END IF;
           BEGIN
               -- try insert
               EXECUTE sql_insert;
               RETURN;
           -- insert violated unique constraint, try loop again
           EXCEPTION WHEN unique_violation THEN
           END;
       END;
       i := i+1;
   END LOOP;
END;
$$
LANGUAGE plpgsql;

該功能有效，通過創建一個小表對其進行了測試。所以問題一定出在我的輸入上，但我仍然看不出問題出在哪裡——除非這個函式非常慢。這不是一個特別大的表，大約有 11,000 行。

但是，此範例輸入永遠不會完成執行：

SELECT upsert(
$$
INSERT INTO accounts 
VALUES (
   '0012000000wa7WzAAI', 'Random company', NULL, 
   'Tier 2', 'Joe BLoggs', 'Joe Bloggs', 'US', 
   TIMESTAMP '2012-12-14 00:00:00', NULL, 'Ex-Customer')
$$, 
$$
UPDATE accounts 
SET account_name='Random Companyh', country=NULL, account_tier='Tier 2', 
   account_owner='Joe Bloggs', sales_person='Joe Bloggs', 
   office_managed='US', date_first_opportunity=TIMESTAMP '2012-12-14 00:00:00', 
   industry=NULL, account_type='Ex-Customer' 
WHERE account_id='0012000000wa7WzAAI'
$$
)

我無法弄清楚為什麼這不起作用，任何幫助將不勝感激。

無限循環的直接原因就是這個。根據文件：

特別注意，EXECUTE改變 的輸出GET DIAGNOSTICS，但不改變FOUND。

但還有更多：

CREATE FUNCTION upsert(_ins text, _upd text)
 RETURNS int AS
$func$
DECLARE
  loop_ct int := 0;
  row_ct  int;
BEGIN
LOOP
  IF loop_ct > 1 THEN   -- terminate on 2nd loop
     RETURN 0;
  END  IF;

  EXECUTE _upd;         -- try update
  GET DIAGNOSTICS row_ct = ROW_COUNT;
  IF row_ct > 0 THEN    -- terminate if update successful
     RETURN row_ct;
  END IF;

  BEGIN                 -- try insert
     EXECUTE _ins;
     GET DIAGNOSTICS row_ct = ROW_COUNT;
     RETURN row_ct;
  EXCEPTION WHEN unique_violation THEN
     -- insert violated unique constraint, keep looping
  END;
  loop_ct := loop_ct + 1;
END LOOP;
END
$func$  LANGUAGE plpgsql;

要點

• GET DIAGNOSTICS而不是FOUND, 如一開始所解釋的。
• 不要在UPDATE. 又貴又沒用。
• 我添加了一個功能：成功時，查詢返回受影響的行數 - 無需額外費用，因為無論如何我們都會獲得行數。可能有用也可能沒用。

其餘不變，只是重新格式化。

我必須添加關於SQL 注入的****警告。將程式碼作為文本傳遞非常容易受到影響。無法清理程式碼。必須小心處理這樣的功能。當然不應該用. 相關答案：SECURITY DEFINER

• UPSERT 函式中的複合類型問題

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