postgresql plpgsql 函数内的事务

Transaction inside a plpgsql function

提问by Yousuf Sultan

I have written a function for automation, mentioned below, which calls some other functions based on some rules. The function is giving me the desired results, but the problem that I am facing is that it does not commit the data after each of the function is processed internally. Once the main function gets completed only then it commits the entire data. I want to do a internal transaction which should commit the data as and when the internal function execution get completed. I tried giving a COMMIT statement after each of the PERFORM statements, but I got an error saying 'cannot begin/end transactions in PL/pgSQL'.

我写了一个自动化的函数，下面提到，它根据一些规则调用一些其他的函数。该函数给了我想要的结果，但我面临的问题是在内部处理每个函数后它没有提交数据。一旦主函数完成，它就会提交整个数据。我想做一个内部事务，它应该在内部函数执行完成时提交数据。我尝试在每个 PERFORM 语句之后给出一个 COMMIT 语句，但是我收到一条错误消息，提示“无法在 PL/pgSQL 中开始/结束事务”。

Can anyone suggest how do I go about doing a transaction inside a function.

任何人都可以建议我如何在函数内进行交易。

CREATE OR REPLACE FUNCTION ccdb.fn_automation_for_updation()
  RETURNS void AS
$BODY$

DECLARE 
sec_col refcursor;
cnt integer;
sec_code ccdb.update_qtable%ROWTYPE;
new_cnt integer;

BEGIN

SELECT COUNT(*)
INTO cnt
FROM ccdb.update_qtable
WHERE status_flag IN (-1,1);

OPEN sec_col FOR
    SELECT * FROM ccdb.update_qtable WHERE status_flag IN (-1,1);

FOR i IN 1..cnt
LOOP

    FETCH sec_col INTO sec_code;

        PERFORM ccdb.o_dtr_update(sec_code.section_code);

        PERFORM ccdb.o_consumer_update_for_update(sec_code.section_code);

        PERFORM ccdb.o_consumer_update_for_insert(sec_code.section_code);

        PERFORM ccdb.o_bills_update_for_update(sec_code.section_code);

        PERFORM ccdb.o_bills_update_for_insert(sec_code.section_code);

        PERFORM ccdb.o_payments_update_for_update_new(sec_code.section_code);

        PERFORM ccdb.o_payments_update_for_insert(sec_code.section_code);

        PERFORM ccdb.o_payments_map_update_for_update(sec_code.section_code);

        PERFORM ccdb.o_payments_map_update_for_insert(sec_code.section_code);

        SELECT COUNT(*) INTO new_cnt FROM ccdb.update_qtable WHERE status_flag IN (-1,1);

        IF new_cnt > cnt
        THEN
            CLOSE sec_col;

            OPEN sec_col FOR
                SELECT * FROM ccdb.update_table WHERE status_flag IN (-1,1);

        cnt := new_cnt;

        END IF;

END LOOP;

CLOSE sec_col;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

回答by Craig Ringer

You cannot perform autonomous transactions in PostgreSQL - its functions don't support it.

您不能在 PostgreSQL 中执行自治事务 - 它的功能不支持它。

You must use DBLink.

您必须使用 DBLink。

See:

看：

• Committing Records into the table while executing a postgreql Function
• Are PostgreSQL functions transactional?
• COMMIT in PostgreSQL stored procedure

• 在执行 postgreql 函数时将记录提交到表中
• PostgreSQL 函数是事务性的吗？
• PostgreSQL 存储过程中的 COMMIT

(Marked CW because I closed the post)

（标记为 CW 因为我关闭了帖子）