用 INSERT / UPDATE 的单个查询替换循环

我正在 PostgreSQL 中编写一个函数。它基本上执行 3 个步骤：

1. 从源表中获取一条记录。
2. 检查目标表中提取的记录的值，如果在目标表中找到记录，则使用提取的记录更新目标表的所有值，否则将提取的记录插入目标表。

如果我写，而不是做这个循环单一查询对于插入/更新，它会比上述方法更快吗？如何通过编写单个查询而不是循环遍历每个记录并进行更新/插入来获得相同的结果。

我目前的方法如下

CREATE OR REPLACE FUNCTION fun1()
  RETURNS void AS
$BODY$DECLARE
   source_tab_row RECORD;

   v_col1 TEXT;
   v_col2 TEXT;
   v_col3 TEXT;
   v_col4 double precision ;
   cnt integer;

BEGIN
    FOR source_tab_row IN (SELECT * FROM source_tab where col5='abc')
LOOP
    v_col1=source_tab_row.col1;   
    v_col2=source_tab_row.col2;
    v_col3=source_tab_row.col3;
    v_col4=source_tab_row.col4;

    select count(*) INTO cnt from dest_tab where col1=v_col1;

     if (cnt =0) then
     -- If records is not found
       INSERT INTO dest_tab(col1, col2, col3,col4)
       VALUES( v_col1, v_col2, v_col3,v_col4)   ;
    else
     --if records found then update it
       update dest_tab set col1=v_col1, col2=v_col2, col3=v_col3,col4=v_col4
       where col1=v_col1;

     end if;         
END LOOP;
END;
$BODY$ LANGUAGE plpgsql;

更好的 SQL

如果你有 PostgreSQL 9.1 或更高版本，你绝对应该使用数据修改CTE http://www.postgresql.org/docs/current/interactive/queries-with.html#QUERIES-WITH-MODIFYING为了这：

WITH x AS (
   UPDATE dest_tab d
   SET    col2 = s.col2
        , col3 = s.col3
   --   , ...
   FROM   source_tab s
   WHERE  s.col5 = 'abc'
   AND    s.col1 = d.col1

   RETURNING col1
   )
INSERT INTO dest_tab(col1, col2, col3, col4)
SELECT s.col1, s.col2, s.col3, s.col4
FROM   source_tab s
WHERE  s.col5 = 'abc'
LEFT   JOIN x USING (col1)
WHERE  x.col1 IS NULL;

正如@Craig 已经发布的那样，基于集合的 SQL 操作通常比迭代单个行要快得多。

然而，这种形式更快、更简单。它还在很大程度上避免了固有的（微小的！）竞争条件。首先，由于这是单个 SQL 命令，因此时间段甚至更短。此外，如果并发事务应在事务之间输入竞争行，UPDATE和INSERT，您会遇到重复密钥违规（前提是您应该有一个 pk / unique 约束）。因为你不查询dest_tab第二次并重复使用原始组INSERT。更快，更好。

如果您看到重复的键违规：没有发生任何不好的事情，只需重试查询即可。

It does not涵盖了并发事务的相反情况DELETE与此同时。在我看来，这确实是不太重要/不常见的情况。

正确的plpgsql

If为此，您使用 plpgsql，简化：

CREATE OR REPLACE FUNCTION fun1()
  RETURNS void AS
$BODY$
DECLARE
   _source source_tab;  -- name of table = type
BEGIN
   FOR _source IN
      SELECT * FROM source_tab where col5 = 'abc'
   LOOP
        UPDATE dest_tab
        SET    col2 = _source.col2  -- don't update col1, it doesn't change
               ,col3 = _source.col3
               ,col4 = _source.col4
        WHERE  col1 = _source.col1;

      IF NOT FOUND THEN  -- no row found
         INSERT INTO dest_tab(col1, col2, col3,col4)
         VALUES (_source.col1, _source.col2, _source.col3, _source.col4);
      END IF;

   END LOOP;
END
$BODY$ LANGUAGE plpgsql;