I wrote an automation function, mentioned below, which calls some other functions based on some rules. The function gives me the desired results, but the problem that I encountered is that it does not capture data after each processing of the function inside. As soon as the main function is completed only then, it captures all the data. I want to make an internal transaction, which should fix the data as and when the execution of the internal function is completed. I tried to give a COMMIT statement after each of the PERFORM statements, but I got the error message "Unable to start / end transactions in PL / pgSQL".
Can anyone suggest how I can make 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;