时间戳

首页 > 解决方案 > 如何让函数在 Postgresql 中的 DO 语句中工作

postgresql - 如何让函数在 Postgresql 中的 DO 语句中工作

问题描述

我有一个长而复杂的 plpgsql 函数，它创建一堆嵌套在 while 语句中的临时表以获得最佳结果。满足条件后，我将结果插入到现有表中，该函数在此处发布的时间太长了，但这是一个示例：

CREATE OR REPLACE FUNCTION public.test_function(id_input integer, val_input numeric)
RETURNS VOID AS
$BODY$

DECLARE
   id_input numeric = $1;
   val_input numeric = $2;

BEGIN

WHILE test_val < 0
LOOP

CREATE TEMP TABLE temp_table AS
SELECT a.existing_val - val_input AS new_val
FROM existing_table a
WHERE a.id = id_input;

test_val := (SELECT new_val FROM temp_table);

val_input := val_input + 1;

END LOOP;

INSERT INTO output_table (id, new_val)
SELECT a.id, a.new_val
FROM temp_table a;

END;
$BODY$
LANGUAGE plpgsql;

如果我这样调用该函数，则该函数可以工作SELECT test_function(1, 1000)但是我想在具有 60,000 多行的表上运行此函数，如下所示：

SELECT test_function(a.id, a.val_input)
FROM data_table a;

它在我使用 data_table 的一个子集时起作用，比如 1000 行。但是，当我在整个表（60,000+ 行）上运行它时，我收到以下错误"AbortTransaction while in COMMIT state"。经过一番阅读，我发现了 COMMITS，所以在我的情况下，插入不会发生，直到函数完成运行，大约需要 4 小时。那么有人知道发生了什么吗？

作为一种解决方法，我尝试将函数嵌套在 DO 语句中，以便立即提交插入：

DO
$do$
DECLARE
    r data_table%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM data_table
    LOOP
    SELECT public.test_function(r.id, r.val_input);
    END LOOP;
END
$do$;

但是然后我收到以下错误“错误：查询没有结果数据的目的地”，我想这意味着我需要重写函数以使用 PERFORM 而不是 SELECT。但是，到目前为止，我还没有任何运气。

有任何想法吗？

标签： postgresqlfunctionplpgsql

解决方案

谢谢大家的建议。我最终使用了 Jim Jones 的建议并将函数转换为允许我在执行 INSERT 后使用 COMMIT 的过程。我也听从了 Jeremy 的建议，从临时表转到 CTE 表。这为我解决了这个问题。

推荐阅读