Re: how to call a stored function on conflict

thank you for your input.
even I create v_Ret as character varying and it still complained the same error.
create or replace FUNCTION ecisdrdm.pr_mig_stg_application_cdim (v_Ret OUT character varying )
ERROR: invalid input syntax for integer: "42P01" CONTEXT: PL/pgSQL function ecisdrdm.pr_mig_stg_application_cdim() line 39 at assignment SQL state: 22P02
I worked around and create errorlog table with errorcode declare as varchar(20) and recreate a function pr_write_error_log (v_ret text).  Again the function combined and select function (pr_write_error_log) fine 



But the function to merge(update)/insert on conflict is still not work even when I run the select function  (pr_mig_stg_application_cdim), not sure this is corrected answer?  It's one row affected.


but two tables are not update/insert any data from one to another.

stg_application_cdim:


application_cdim:


my "on conflict" is:
INSERT INTO ecisdrdm.application_cdim AS prod (prod.application_id, prod.receipt_number,prod.init_frm_id, prod.frm_typ_id,      prod.sbmtd_dt_id, prod.crtd_user_id, prod.init_src_sys_id, prod.init_svc_ctr_id, prod.mig_filename)  SELECT stg.application_id, stg.receipt_number, stg.init_frm_id, stg.frm_typ_id, stg.sbmtd_dt_id, stg.crtd_user_id,   stg.init_src_sys_id, stg.init_svc_ctr_id, stg.mig_filename  FROM ecisdrdm.stg_application stg ON CONFLICT (application_id)  DO UPDATE  SET ( prod.init_frm_id, prod.frm_typ_id,prod. sbmtd_dt_id, prod.crtd_user_id,     prod.init_src_sys_id, prod.init_svc_ctr_id, prod.mig_filename, prod.mig_modified_dt )=  (SELECT stg.init_frm_id, stg.frm_typ_id, stg.sbmtd_dt_id, stg.crtd_user_id,      stg.init_src_sys_id, stg.init_svc_ctr_id, stg.mig_filename, current_timestamp FROM ecisdrdm.stg_application_cdim stg WHERE prod.receipt_number = stg.receipt_number );
would you please tell me what the issue here?
thank you so much.
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 

    On Tuesday, October 8, 2019, 03:29:59 PM EDT, David G. Johnston <[email protected]> wrote:  
 
 On Tue, Oct 8, 2019 at 11:56 AM Pepe TD Vo <[email protected]> wrote:
ERROR: invalid input syntax for integer: "42P01"CONTEXT: PL/pgSQL function ecisdrdm.pr_mig_stg_application_cdim() line 41 at assignmentSQL state: 22P02

You made an assumption that the error code was an integer.  As the error message is pointing out one possible value of the error code is "42P01" which is not an integer.  You need to fix your code to match reality - that the error code is an alphanumic.

may I know how to execute the stored function?

The fact that the function provoked an error means that it was executed.....
David J.