title: Transaction inside a plpgsql function
body: <p>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'.</p>
  
  <p>Can anyone suggest how do I go about doing a transaction inside a function.</p>
  
  <pre><code>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 &gt; 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;
  </code></pre>