how to call a stored function on conflict

From: Pepe TD Vo <pepevo(at)yahoo(dot)com>
To: Pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: how to call a stored function on conflict
Date: 2019-10-08 18:56:29
Message-ID: 1496660547.3525362.1570560989743@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello experts,
I have a store function on conflict to update and insert features information from one table to another table  
My code is:
create or replace FUNCTION ecisdrdm.pr_mig_stg_application_cdim (v_Ret OUT bigint ) as $$declare         v_ErrorCode             bigint;        v_ErrorMsg              varchar(512);        v_Module                varchar(32) = 'pr_mig_stg_application_cdim';
begin
------ MERGING: STG_APPLICATION_CDIM into APPLICATION_CDIM----
INSERT INTO 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 stgON 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_timestampFROM ecisdrdm.stg_application_cdim stgWHERE prod.receipt_number = stg.receipt_number);           RETURN;
   ----   -- Set the return code to 0   ----
   v_Ret := SQLSTATE;
------ Exception error handler----exception   when others then v_ErrorCode := SQLSTATE; v_ErrorMsg  := SQLERRM; v_Ret       := v_ErrorCode;
---- -- Commit the record into the ErrorLog ---- PERFORM pr_write_error_log( v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMsg );
---- -- Intentionally leaving the "commit" to application ----end;$$ language plpgsql;
and my other function for pr_write_error_log is 
CREATE OR REPLACE FUNCTION ecisdrdm.pr_write_error_log ( v_os_user character varying, v_host character varying, v_module character varying, v_errorcode integer, v_errormsg character varying)    RETURNS void    LANGUAGE 'plpgsql'
    COST 100    VOLATILE AS $BODY$
BEGIN START TRANSACTION; INSERT INTO ecisdrdm.errorlog(tstamp, os_user, host, module, errorcode, errormsg) VALUES    (CURRENT_TIMESTAMP, CURRENT_USER, inet_server_addr(), v_module, v_ErrorCode, v_ErrorMsg );
        /* commit; */ end;
$BODY$;
ALTER FUNCTION ecisdrdm.pr_write_error_log (character varying, character varying, character varying, integer, character varying)    OWNER TO "PSmasteruser";

they all created successfully.
when I execute the function of pr_mig_stg_application_cdim as "select ecisdrdm.pr_mig_stg_application_cdim(); "
ERROR: invalid input syntax for integer: "42P01"CONTEXT: PL/pgSQL function ecisdrdm.pr_mig_stg_application_cdim() line 41 at assignmentSQL state: 22P02
may I know how to execute the stored function?  What did wrong on my queries?
thank you for your help.
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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Holger Jakobs 2019-10-08 19:29:03 Re: how to call a stored function on conflict
Previous Message Daniel Malungu 2019-10-07 13:49:26 Installing repmgr in centos 7 from source