在 PL/pgSQL 函式中使用 LIMIT / OFFSET 進行更新

在我的 Postgres 9.2 數據庫中，我有以下內容SELECT，將由 PL/pgSQL 函式使用：

SELECT
     id
FROM
     tablea
WHERE
     migrated = false;

我想每次獲取 2000 條記錄並執行以下操作：

CREATE or REPLACE FUNCTION migrate_data()
RETURNS integer AS
$$
declare
       row record;

BEGIN

FOR row IN EXECUTE '
       SELECT
             id
       FROM
             tablea
       WHERE
             migrated = false
'
LOOP

INSERT INTO tableb (id)
VALUES (row.id);

UPDATE tablea a SET migrated = yes WHERE a.id = row.id;

END LOOP;

RETURN num_rows; -- I want it to return the number of processed rows

END

$$ language 'plpgsql';

如何告訴函式每次呼叫處理 2000 條記錄？它必須從下一次呼叫時離開的地方開始。

例子：

select migrate_data(); -- id 0 to id 2000

select migrate_data(); -- id 2001 to id 4000

select migrate_data(); -- id 4001 to id 6000

select migrate_data(); -- id 6001 to id 8000

等等。

您可以使用數據修改 CTE執行單個 SQL 命令。GET DIAGNOSTICS然後在 plpgsql 函式中獲取行數：

CREATE OR REPLACE FUNCTION migrate_data(OUT row_ct int)
 LANGUAGE plpgsql AS
$func$
BEGIN
  WITH sel AS (
     SELECT id
     FROM   tablea
     WHERE  migrated = false
     ORDER  BY id   -- to proceed in deterministic order
     LIMIT  2000
     FOR    UPDATE  -- only for concurrent write access
     )
   , upd AS (
     UPDATE tablea a 
     SET    migrated = TRUE
     FROM   cte
     WHERE  a.id = sel.id
     )
  INSERT INTO tableb(id)
  TABLE  sel;  -- shorthand for: SELECT * FROM sel

  GET DIAGNOSTICS row_ct = ROW_COUNT;  -- writes to OUT param directly
END
$func$;

有關的：

• Postgres 更新…限制 1
• 如何訪問 PL/pgSQL 中 RETURN 累積的行數

您可能只使用沒有函式包裝器的 SQL 語句。可能作為準備好的聲明。

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