sql - 将循环替换为针对 INSERT/UPDATE 的单个查询

Question

我正在 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;

Answer 1

更好的 SQL

如果您有 PostgreSQL 9.1 或更高版本，您绝对应该使用 data-modifying CTE为此:

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 重新使用原始集。更快，更好。

如果您看到重复键违规:没有发生任何不良情况，只需重试查询即可。

它不涵盖并发事务同时DELETE一行的相反情况。 IMO，这确实是不太重要/不太常见的情况。

正确的plpgsql

如果为此使用 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;