commit & rollback

Hello experts,
I have a main functions call the child functions.  If one of the child function break, I want the main function stop and tell me what the error.  From my test, if I break one from the main function and no other functions execute. That's fine, but if I break the child, the main function notice the error and still execute the rest of function, which I want it stop and tell me what caused the error as same as the one break from the main function.  How do I commit and rollback the function.
here is my main function script:
CREATE OR REPLACE FUNCTION ecisdrdm.pr_merge_staging_tables( OUT v_ret text)    RETURNS text    LANGUAGE 'plpgsql'
    COST 100    VOLATILE AS $BODY$    DECLARE
   v_errorcode      text;   v_errormsg       varchar(512);   v_module         varchar(32) = 'pr_merge_staging_tables';   v_host varchar(32) = inet_server_addr();
begin
PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Starting MERGE Process' ); begin v_ret = '0';
if v_ret = '0' then --- --- Perform the merge on APPLICATION_CDIM ---      PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Started merging STG_APPLICATION_CDIM' ); PERFORM ecisdrdm.pr_mig_stg_application_cdim(); -- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION v_ret = '0'; PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_APPLICATION_CDIM' ); end if; /* exception     when others then GET STACKED DIAGNOSTICS v_ret = PG_EXCEPTION_CONTEXT; RAISE INFO 'Error Name:%',SQLERRM;        RAISE INFO 'Error State:%', SQLSTATE;        RAISE INFO 'Error Context:%', v_errorcode; */ end;
---- ---  2. STG_BNFT_CURR_FACT ----    if v_ret = '0' then
--- --- Perform the merge on BNFT_CURR_FACT ---
PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Started merging STG_BNFT_CURR_FACT' ); PERFORM ecisdrdm.pr_mig_stg_bnft_curr_fact(); -- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION v_ret = '0'; PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_BNFT_CURR_FACT' ); end if;
---- --  3. STG_BNFT_FACT ----    if v_ret = '0' then --- --- Perform the merge on BNFT_FACT ---      PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Started merging STG_BNFT_FACT' ); PERFORM ecisdrdm.pr_mig_stg_bnft_fact(); -- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION v_ret = '0'; PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_BNFT_FACT' ); end if;
---- --  4. STG_BNFT_HIST_ACTN_LDIM ----    if v_ret = '0' then --- --- Perform the merge on BNFT_HIST_ACTN_LDIM ---      PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Started merging STG_BNFT_HIST_ACTN_LDIM ' ); PERFORM ecisdrdm.pr_mig_stg_bnft_hist_actn_ldim (); -- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION v_ret = '0'; PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_BNFT_HIST_ACTN_LDIM ' );     end if;
---- --  5. STG_CNTRY_ST_CDIM ----    if v_ret = '0' then --- --- Perform the merge on CNTRY_ST_CDIM ---      PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0','Started merging STG_CNTRY_ST_CDIM ' ); PERFORM ecisdrdm.pr_mig_stg_cntry_st_cdim(); -- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION v_ret = '0'; PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_CNTRY_ST_CDIM '); end if;
---- --  6. STG_FRM_CDIM ----    if v_ret = '0' then --- --- Perform the merge on FRM_CDIM ---      PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Started merging STG_FRM_CDIM ' ); PERFORM ecisdrdm.pr_mig_stg_frm_cdim(); -- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION v_ret = '0'; PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_FRM_CDIM ' ); end if;
------ This does the final commit or rollback for the entire merge process.---
if v_ret = '0' then PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished MERGE PROCESS - COMMIT WORK' ); /* commit work; */ COMMIT; else PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished MERGE PROCESS - ROLLBACK WORK ' ); /* rollback work; */ ROLLBACK;
RAISE NOTICE 'PROGRAM_ERROR'; end if;
------- Exception error handler----
exception   when others then        v_errorcode := SQLSTATE;        v_errormsg  := SQLERRM;
PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, v_errorcode, v_errormsg);
END;$BODY$SECURITY DEFINER;

if work fine without "rollback;" and when I put "rollback;" in, I get "invalid transaction termination without tell me which child function's error.  Now, I put it back the way it is w/o commit and rollback.  All I get is "Finished MERGE PROCESS - ROLL BACK".... the weird thing is every single change in the main function, all still stop working.
 I can't seem to find  anything else a good thing to do with commit and rollback, would you please tell me what cause the problem.  Now all stop only the last statement "merge process - roll back" stated.

thank you.
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success As I can't seem to find the script, anything else a good thing to do after doing pg_upgrade to prime the DB?