Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iHuf5-0000CW-G9 for pgsql-admin@arkaria.postgresql.org; Tue, 08 Oct 2019 18:56:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iHuf4-0005R9-1h for pgsql-admin@arkaria.postgresql.org; Tue, 08 Oct 2019 18:56:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iHuf3-0005Qw-Dd for pgsql-admin@lists.postgresql.org; Tue, 08 Oct 2019 18:56:41 +0000 Received: from sonic311-14.consmr.mail.bf2.yahoo.com ([74.6.131.124]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iHuez-0000cY-Js for pgsql-admin@postgresql.org; Tue, 08 Oct 2019 18:56:41 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1570560994; bh=I5wOmGSVtOSJru/MHHx5zhVu3dhsX4MWY1wxcCyUMJ8=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=SahyWorFtoXCePbsgNd//rPv/GHklP1fpX6HjWaSTehdR023tOf/81u2BVy2QcNGcvYqvW8iRGMNlL0MPww9V0Ky3GJqWk6hlFfzKOVW+73Bu9e7cqSUnaJ46xyfCMn8pKsLxWdgZA5ZC9/mjz0p4p7z+RYqTxBc+IjnefcvnExClWTRvTVW7PN7cT/O1ddG6aP+Adx30+1E6DguYGgr9tmbXpmaa+9IAY7rXRAfxmHgUpyY0LLfU3pSaDOQeXJDg2Ry4AbYlteY+pv1HM+Cksi+VnhumNQLTOjdaSo2GhBy9i8xGbnhlBBn+KBmm1itgNjiitwP4NdS/dJkk/lZbw== X-YMail-OSG: aHNObjUVM1ltE4V3Afh453RYFs01ZL27HgTIoDAZ_EgSAbSnl4U2Kauk1qcUMCz F_kkKBfK8MScDCGagaShhjhYRrB3gAs2SBW0zrO1BJB4DKNZWgVSjyAsluI9AQSTsl35g1scinxI VgzexRpP1a_o3io3WVGVHeMDEJH0huw6F_AyvQxhwfGCQziOApRelXY4oHvva_7GMrw2N7.X1OsJ 1U0w53XC014XG3BHEAkIS3rGADa9VlmzDuCJ6hbgz.7RBmzA1iESuJRtT.BG6K84q1eFUR5Rq0lN O2j3m6kQSnShEYxTj8ZoHKnD6eet.WIMKg5XvllYWFauohfZ94fjZt_.MXJ5EM1EfPJKRt1kj.4w dPTdDTKqs4EtSufX1655bqGa3GWJl6ye9hL2L.Gx2UPAazWKHXS42jIRgwxAjG550R7FbHprSeWh 3UpcR_kgxt_r6M7P9RVAaugZ6V03wSqHZO5c7XOrOwgjVgUgINJvpeKLOkm42MXVcv4tfy6R7ByR bYchxC_KIzshJW7dpQJLM6vUaXPTflF4BpcmNRkFrjFYPS.UdNLtDZE3nCrPcfLKJP6MkybZrYzg CA9XdqwtoCsGvbCKYrk6HGCo1ZMITUloR2TZ_dVC6C3C4o4JawdtXWY8xXPlNI_BJ7jVpkP03YUA Iqlb4EO0lClb6JwTtQ5eKKctMYbqf48TxIeiuNKqNhWkW.aUMGOTK2WBmTwxUMOxwnyDiEXOZ18e NWa0NjHhqJW0xO4n5KtkPGQiXaqubOYx24fo.abNO981.7UiokJOuLHqO31gZudFLxmKqw4FwbV4 uoOiSvRco.4v7RNCpLDRY3JhtUOMVzT2aOw3PocV9C4JnBjKtUQYNgsUlLeTvtUT23jGZRW9q4Wr PSPBTOUw3HuxReDkgNDy7vO6elW3Ah5oRkT_MP4Kbe4JghEVeSIEKRH4bargSZM.WjDvwmNmVnAd 0Ie1C8JmErDYET.cnWuOrX12PPG.wzNE0Ps5vh5KHdb8Uq3czVknyRXK1h8yy.WeP.z.eSasQFYF uYzjyMn_LjY8mwudov_lCF5AWLqOEOduDIVBsDhIXj3waB91Mo8PnqjQ.jn6QaUhaQbpVa6xq46B mC.gh01s9XMwbnI3eXs6HbqO7UGzhe8CY6MBjvC_zqfKvclYJO86ZES1oehvD0LNQ5t8gPUphh5U C.G.fC_zuCrhlHQ0yK6bmAdyi2UjYXipQP_GFz1swWwD9yAe_ZHAWW2u3_ZeMBdkjNfptA7Gle34 IJuzuYHJviJ1MRkIDZxN97brWvnS2UGCULAuw8A8XuKlllswmQwvMnn1yqA2TiLT_9pBvaEgOJ7B ACVo- Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.bf2.yahoo.com with HTTP; Tue, 8 Oct 2019 18:56:34 +0000 Date: Tue, 8 Oct 2019 18:56:29 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Pgsql-admin Message-ID: <1496660547.3525362.1570560989743@mail.yahoo.com> Subject: how to call a stored function on conflict MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3525361_1502180383.1570560989740" References: <1496660547.3525362.1570560989743.ref@mail.yahoo.com> X-Mailer: WebService/1.1.14448 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.90 Safari/537.36 Content-Length: 17651 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3525361_1502180383.1570560989740 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello experts, I have a store function on conflict to update and insert features informati= on from one table to another table=C2=A0=C2=A0 My code is: create or replace FUNCTION ecisdrdm.pr_mig_stg_application_cdim (v_Ret OUT = bigint )=C2=A0as $$declare=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorCode=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0bigint;=C2=A0 =C2=A0 =C2=A0 = =C2=A0 v_ErrorMsg=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 varchar(5= 12);=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_Module=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 varchar(32) =3D 'pr_mig_stg_application_cdim'; begin ------ MERGING: STG_APPLICATION_CDIM into APPLICATION_CDIM---- INSERT INTO application_cdim AS prod (prod.application_id, prod.receipt_num= ber,prod.init_frm_id, prod.frm_typ_id,=C2=A0=C2=A0 prod.sbmtd_dt_id, prod.c= rtd_user_id, prod.init_src_sys_id, prod.init_svc_ctr_id, prod.mig_filename)= =C2=A0SELECT stg.application_id, stg.receipt_number, stg.init_frm_id, stg.f= rm_typ_id, stg.sbmtd_dt_id, stg.crtd_user_id, stg.init_src_sys_id, stg.init= _svc_ctr_id, stg.mig_filename=C2=A0FROM ecisdrdm.stg_application stgON CONF= LICT (application_id)=C2=A0 DO UPDATE=C2=A0SET ( prod.init_frm_id, prod.frm= _typ_id,prod. sbmtd_dt_id, prod.crtd_user_id,=C2=A0 prod.init_src_sys_id, p= rod.init_svc_ctr_id, prod.mig_filename, prod.mig_modified_dt)=3D=C2=A0(SELE= CT stg.init_frm_id, stg.frm_typ_id, stg.sbmtd_dt_id, stg.crtd_user_id,=C2= =A0 stg.init_src_sys_id, stg.init_svc_ctr_id, stg.mig_filename, current_tim= estampFROM ecisdrdm.stg_application_cdim stgWHERE prod.receipt_number =3D s= tg.receipt_number);=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0RETURN; =C2=A0 =C2=A0----=C2=A0 =C2=A0-- Set the return code to 0=C2=A0 =C2=A0---- =C2=A0 =C2=A0v_Ret :=3D SQLSTATE; ------ Exception error handler----exception=C2=A0 =C2=A0when others then v_= ErrorCode :=3D SQLSTATE; v_ErrorMsg=C2=A0 :=3D SQLERRM; v_Ret=C2=A0 =C2=A0 = =C2=A0 =C2=A0:=3D v_ErrorCode; ---- -- Commit the record into the ErrorLog ---- PERFORM pr_write_error_lo= g( v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMsg ); ---- -- Intentionally leaving the "commit" to application ----end;$$ langu= age plpgsql; and my other function for pr_write_error_log is=C2=A0 CREATE OR REPLACE FUNCTION ecisdrdm.pr_write_error_log ( v_os_user characte= r varying, v_host character varying, v_module character varying, v_errorcod= e integer, v_errormsg character varying)=C2=A0 =C2=A0 RETURNS void=C2=A0 = =C2=A0 LANGUAGE 'plpgsql' =C2=A0 =C2=A0 COST 100=C2=A0 =C2=A0 VOLATILE=C2=A0AS $BODY$ BEGIN START TRANSACTION; INSERT INTO ecisdrdm.errorlog(tstamp, os_user, hos= t, module, errorcode, errormsg) VALUES =C2=A0 =C2=A0(CURRENT_TIMESTAMP, CUR= RENT_USER, inet_server_addr(), v_module, v_ErrorCode, v_ErrorMsg ); =C2=A0 =C2=A0 =C2=A0 =C2=A0 /* commit; */ end; $BODY$; ALTER FUNCTION ecisdrdm.pr_write_error_log (character varying, character va= rying, character varying, integer, character varying)=C2=A0 =C2=A0 OWNER TO= "PSmasteruser"; they all created successfully. when I execute the function of pr_mig_stg_application_cdim as "select ecisd= rdm.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: 22P0= 2 may I know how to execute the stored function?=C2=A0 What did wrong on my q= ueries? thank you for your help. Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir 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 t= o 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 ------=_Part_3525361_1502180383.1570560989740 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello experts,

I hav= e a store function on conflict to update and insert features information fr= om one table to another table  

My code is:

create or replace FUNCTION ecisdrdm.p= r_mig_stg_application_cdim (v_Ret OUT bigint ) 
as $$
= declare 
        v_ErrorCod= e             bigint;
        v_= ErrorMsg              varchar(512);
    &n= bsp;   v_Module               = varchar(32) =3D 'pr_mig_stg_application_cdim';

begin

----
-- MERGING: STG_APPLICATION_CDIM into APPLICATION_CDIM<= /div>
----

INSERT INTO application_cdim AS p= rod (prod.application_id, prod.receipt_number,prod.init_frm_id, prod.frm_ty= p_id,  
=09prod.sbmtd_dt_id, pr= od.crtd_user_id, prod.init_src_sys_id, prod.init_svc_ctr_id, prod.mig_filen= ame) 
S= ELECT stg.application_id, stg.receipt_number, stg.init_frm_id, stg.frm_typ_= id, stg.sbmtd_dt_id, stg.crtd_user_id,
=09stg.init_src_sys_id, stg.init_svc_ctr_id, stg.mig_filename 
FROM ecisdrdm.stg_app= lication stg
ON CONFLICT (application_id)  DO UPDATE 
SET ( prod.init_frm_id, prod.frm_t= yp_id,prod. sbmtd_dt_id, prod.crtd_user_id, 
)
= =3D 
(S= ELECT stg.init_frm_id, stg.frm_typ_id, stg.sbmtd_dt_id, stg.crtd_user_id,
 =09stg.init_src_sys_id, stg.init_svc= _ctr_id, stg.mig_filename, current_timestamp
FROM ecisdrdm.stg_application_cdim stg
WHERE prod.rece= ipt_number =3D stg.receipt_number
);           
RETURN;
<= div style=3D"">
   ----
   -- Set the re= turn code to 0
   ----

   v_Ret :=3D SQLSTATE;

----
-- Exception error handler
----
exception
   when others then
=09v_ErrorCode :=3D SQLSTATE;
=09= v_ErrorMsg  :=3D SQLERRM;
=09v_Ret&n= bsp;      :=3D v_ErrorCode;

=09-= ---
=09-- Commit the record into the Erro= rLog
=09----
= = =09PERFORM pr_write_error_log( v_os_user, v_host, v_module, v_ErrorC= ode, v_ErrorMsg );

= =09----
=09-- Intentionally leaving the "commit" to application=
=09----
end;
$$ language plpgsql;

and my other function for pr_write_error_log is&nbs= p;

CREATE OR REPLACE FUNCTION ecisdrdm.pr_write_error_log (
=09v_os_user character varying,
=09v_host character varying,
=09v_module character var= ying,
=09v_errorcode integer,
=09v_errormsg character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
=
BEGIN
=
=09START TRANSACTION;
=09INSERT INTO ecis= drdm.errorlog(tstamp, os_user, host, module, errorcode, errormsg)
=09VALUES
=09   (CURRENT_TIM= ESTAMP, CURRENT_USER, inet_server_addr(), v_module, v_ErrorCode, v_ErrorMsg= );

        /* commit= ; */
=09=09
end;

$BODY$;
ALTER FUNCTION ecisd= rdm.pr_write_error_log (character varying, character varying, character var= ying, integer, character varying)
    OWNER TO "PSmasteruser";


they all created successfully.
<= div dir=3D"ltr" data-setdir=3D"false" style=3D"">
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
<= br clear=3D"none">No one in this world is pure and perfect.  If yo= u avoid people for their mistakes you will be alone. So judge less, love an= d 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 generous= ly **Care deeply **Speak kindly.
*** Genuin= ely rich *** Faithful talent *** Sharing success
<= /div>
------=_Part_3525361_1502180383.1570560989740--