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 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
);
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;
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 assignment
SQL 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.
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
