plpgsql函数内部的事务 [英] Transaction inside a plpgsql function
问题描述
我已经编写了下面提到的自动化功能,该功能根据一些规则调用了一些其他功能.该函数给了我想要的结果,但是我面临的问题是,在内部处理了每个函数之后,它没有提交数据.一旦main函数完成,就提交全部数据.我想做一个内部事务,应该在内部函数执行完成时提交数据.我尝试在每个PERFORM语句之后给出一个COMMIT语句,但是遇到一个错误,提示无法在PL/pgSQL中开始/结束事务".
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'.
任何人都可以建议我如何在函数内进行事务处理.
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;
推荐答案
您无法在PostgreSQL中执行自主事务-它的功能不支持它.
You cannot perform autonomous transactions in PostgreSQL - its functions don't support it.
您必须使用DBLink.
You must use DBLink.
请参阅:
- Committing Records into the table while executing a postgreql Function
- Are PostgreSQL functions transactional?
- COMMIT in PostgreSQL stored procedure
(标记为CW,因为我关闭了帖子)
(Marked CW because I closed the post)
这篇关于plpgsql函数内部的事务的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!