Committing transactions while executing a postgreql Function

This can be done using dblink. I showed an example with one insert being committed you will need to add your while loop logic and commit every loop. You can http://www.postgresql.org/docs/9.3/static/contrib-dblink-connect.html

CREATE OR REPLACE FUNCTION log_the_dancing(ip_dance_entry text)RETURNS INT AS$BODY$    DECLARE    BEGIN        PERFORM dblink_connect('dblink_trans','dbname=sandbox port=5433 user=postgres');        PERFORM dblink('dblink_trans','INSERT INTO dance_log(dance_entry) SELECT ' || '''' || ip_dance_entry || '''');        PERFORM dblink('dblink_trans','COMMIT;');        PERFORM dblink_disconnect('dblink_trans');         RETURN 0;    END;$BODY$  LANGUAGE plpgsql VOLATILE  COST 100;ALTER FUNCTION log_the_dancing(ip_dance_entry text)  OWNER TO postgres;BEGIN TRANSACTION;  select log_the_dancing('The Flamingo');  select log_the_dancing('Break Dance');  select log_the_dancing('Cha Cha');ROLLBACK TRANSACTION;--Show records committed even though we rolled back outer transactionselect *from dance_log;

What you're asking for is generally called an autonomous transaction.

PostgreSQL does not support autonomous transactions at this time (9.4).

To properly support them it really needs stored procedures, not just the user-defined functions it currently supports. It's also very complicated to implement autonomous tx's in PostgreSQL for a variety of internal reasons related to its session and process model.

For now, use dblink as suggested by Bob.

If you have the flexibility to change from function to procedure, from Postgresql 12 onwards you can do internal commits if you use procedures instead of functions, invoked by CALL command. Therefore you're function will be changed to a procedure and invoked with CALL command: e.g:

    CREATE PROCEDURE transaction_test2()    LANGUAGE plpgsql    AS $$    DECLARE     r RECORD;    BEGIN      FOR r IN SELECT * FROM test2 ORDER BY x LOOP        INSERT INTO test1 (a) VALUES (r.x);        COMMIT;      END LOOP;    END;    $$;    CALL transaction_test2();

More details about transaction management regarding Postgres are available here: https://www.postgresql.org/docs/12/plpgsql-transactions.html