Re: how to call a stored function on conflict
От | Holger Jakobs |
---|---|
Тема | Re: how to call a stored function on conflict |
Дата | |
Msg-id | 725f0501-c6db-f343-aff6-30d70040ed07@jakobs.com обсуждение исходный текст |
Ответ на | how to call a stored function on conflict (Pepe TD Vo <pepevo@yahoo.com>) |
Список | pgsql-admin |
Hi Pepe,
the SQLSTATE is not an integer or bigint, so it cannot be stored in a variable declared as bigint.
See https://www.postgresql.org/docs/current/errcodes-appendix.html
Regards,
Holger
Am 08.10.19 um 20:56 schrieb Pepe TD Vo:
Hello experts,I have a store function on conflict to update and insert features information from one table to another tableMy code is:and my other function for pr_write_error_log iscreate or replace FUNCTION ecisdrdm.pr_mig_stg_application_cdim (v_Ret OUT bigint )as $$declarev_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_filenameFROM ecisdrdm.stg_application stgON CONFLICT (application_id) DO UPDATESET ( 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----exceptionwhen others thenv_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;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 voidLANGUAGE 'plpgsql'COST 100VOLATILEAS $BODY$BEGINSTART 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 assignment SQL state: 22P02may 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
--
Holger Jakobs, Bergisch Gladbach
instant messaging: xmpp:holger@jakobs.com
+49 178 9759012 oder +49 2202 817157
В списке pgsql-admin по дате отправления: