COMMIT IN STORED PROCEDURE WHILE IN A LOOP

[Ravi Krishna <s_ravikrishna@xxxxxxx>]

AWS Aurora based on PG 13

I am writing a sproc to copy a schema into another.  Here is the relevant portion of the code.
Basically I want to commit after every table is created.  In big schemas with hundreds of table I do not want to run entire operation in one transaction.

I am getting error at COMMIT -> cannot commit while a subtransaction is active.
Is commit not possible in a loop


 

BEGIN

  FOR object IN

    SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema

          AND table_type = 'BASE TABLE'

  LOOP


      buffer := dest_schema || '.' || object;

      BEGIN

                    sql_stmt := 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object;

                    sql_stmt :=  sql_stmt || ' INCLUDING COMMENTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING GENERATED INCLUDING INDEXES)' ;

                    --RAISE NOTICE '%' , sql_stmt ;

                    execute sql_stmt ;

                    COMMIT;

                    sql_stmt := 'INSERT INTO ' || buffer || ' OVERRIDING SYSTEM VALUE SELECT * FROM ' || source_schema || '.' || object ;

                    --RAISE NOTICE '%' , sql_stmt ;

                    execute sql_stmt ;

                    COMMIT;

            EXCEPTION

            WHEN OTHERS THEN

            GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,

                                                                        text_var2 = PG_EXCEPTION_DETAIL,

                                                                        text_var3 = PG_EXCEPTION_HINT;

             print_msg := 'ERROR:->' || text_var1 || '|' || text_var2 || '|' || text_var3 ;

             RAISE NOTICE '%' , print_msg ;

            END ;

  END LOOP;

END;