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 1iK3D6-0003Oy-PH for pgsql-admin@arkaria.postgresql.org; Mon, 14 Oct 2019 16:28:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iK3C6-0003zh-ET for pgsql-admin@arkaria.postgresql.org; Mon, 14 Oct 2019 16:27:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iK3C5-0003za-Kx for pgsql-admin@lists.postgresql.org; Mon, 14 Oct 2019 16:27:38 +0000 Received: from sonic309-14.consmr.mail.bf2.yahoo.com ([74.6.129.124]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iK3C2-0006bo-RG for pgsql-admin@postgresql.org; Mon, 14 Oct 2019 16:27:36 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1571070452; bh=Pi4uGMY3fOaDJhb6RTDiSSfI4lWuxLStK2AGATYlCDw=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=SJyPucyJU5KpMFOJMwM+LCLmcNVqJwvGcxYUEtZwHdU+bI2nWJTuS35k2/IEuFJ08z4b1G2SHhwPXs6L+fYi9ZrcxFIGPMXl/VA5cDg1p6jAjZ6czgeIuHZvXVO4wW/lSMIVT232wNenrOrU/CHWbVRJMy/8tytr9Fyu9AB+pFcUPfQXkV4GiBlOrnS2wU2rnrtwATj0Fm9KcVESpVrmlVXJkCNRA6+Oppv/cv+ZJ2A4XsInhK4n+qWiJqTVpuSpccu27pkL5IVK813xF86pIeZh52mkIy8wTdwKBkiP/92NVILIR7OshCfLH59TFTzNWzUN/gnc5TybxCJ/45AEaw== X-YMail-OSG: xWXvLsQVM1l6Gp92npcw4R4.siKBwFVlDqEz9CXmUc8PxFEmuLoR51kUvAAghGZ s57KTUWXI_c_Xbq0.PBzA14ursXL53PEx9h7w44jWwozdUwXb3GzygPjIer3Lbni1_nKdXUQ5Mhe Ti4RUpjL8NR4SJRHU0vmjsVcgdWR_PGE6w5qG95JoXkIrpV7jH.9yUW6n1bXgmZ4dBsjmxCnZAiF 0q15Usps.FaVRHB1wO_ICnLdaprjE4eO5s5fQE.Jjd9KUoH9mdE8nJpOluAlqv.bw20LPbpNJq2e 687G1u0kH3W2bteSPSldip7Sap2Dsr7ED2YmXcHrqq.jcqTMNme9kD.wnWmLgmPb7SKvP2FWH4XN NFGQPD1Rexg6x7.nb9SscliPyJbQnRXrG_EPoXmmWlQ72DXmXOq5f7_w7r7LA0R_5Gmyuoloew17 gCvst6FBhRRFk5WL7mI791brCOOqS862mM.R9WNK4I6UJL2u1ZfA7IEFqTG41ehr0ahwntUS7qMJ JBVHZkLYUQEOwlc9g77TM_FEbPWeaURl5zI01nrrkxRFiS02WfdkJP3MXiR_bY0hZlGJCVzGt5Qv U5hdgZKuFAYdOreLfHW7XgO5RlMIqHCaY5sCov30Ptq.3OTwdbDq7Dn4c13h9Gk6AmYSzMc5AI2s mWywJ4Ea0Fw7.XyR0oMQ0HC2pCm.59PyeN7TJYP5TmdUASLmPbnyTPwZRJS0lOSFnfuxUjOcd7Dk wx4xy1klK0EFrG42Gt8YpeDVj0wQOWdeOB7UfD00gloIZinE85d8tjBznjMdqx8p1DnoSIds5LKq ejVidqmDEP6JI7VLyoj.g8fVcI54bWg4man5nhm4GYmwaMYZG9kcy81EBTk9GKQfk3G45XP_.dcC OkKIU0Hb2kQVgM3zEqbKafaRHdDIGre8QQocxfB8jcdDmqkJBda..wY6.ZSHPkflt4SO.CpBdLWH DMQkN_OTy9KeumHKt9_iEHQVutupdymzmCXmXKM.9N6rCx.m1802dxw3heEF6fR4HQOjTI7j8CF7 zMyv8T2x7.a5DjNvjHv3R0vMjmAY8b8Zbl0hIieubDo2ONay64_dyGwrLD5JXx_swsTW6g58pORG 3J5nqDoh.BcOGh3bXFM_f5siuCEtBxXBOHj08DtsaSeOCQ9ogs7uTgmz6X9avC788_ZwdyRee8u5 lCEVdW15nwA9stjDUThqHiwFGEB7PWOFjsLekE8Ag19Gh70qH.bBGWGLV2oBchJ4z_loXHvITsjW OFfc1p_BaYgG7HClHboLbnUkgSNyMrAFMhUgdmA1ZAfqLURrM5TOjHfiJJWYOe44jXLI3w.QKhB9 bIIbc Received: from sonic.gate.mail.ne1.yahoo.com by sonic309.consmr.mail.bf2.yahoo.com with HTTP; Mon, 14 Oct 2019 16:27:32 +0000 Date: Mon, 14 Oct 2019 16:27:31 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Pgsql-admin Message-ID: <292566289.866009.1571070451686@mail.yahoo.com> Subject: can't call function to delete the table MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_866008_186433472.1571070451684" References: <292566289.866009.1571070451686.ref@mail.yahoo.com> X-Mailer: WebService/1.1.14498 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Content-Length: 13509 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_866008_186433472.1571070451684 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello Experts, Would you please help me why I can't call the function to delete table and = insert data from another table?=C2=A0=C2=A0Simple deletion and insert from = one to other are fine=C2=A0 DELETE FROM bnft_hist_actn_ldim; INSERT INTO bnft_hist_actn_ldim=C2=A0SELECT stg.bnft_hist_actn_id, stg.bnft= _hist_actn_src_cd, stg.bnft_hist_actn_desc, stg.mig_filenameFROM stg_bnft_h= ist_actn_ldim stg; -----but when I put in the stored function, it's not working. My stored function script is: CREATE OR REPLACE FUNCTION pr_mig_stg_bnft_hist_actn_ldim(OUT v_Ret integer= )AS $$ 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=A0varchar(32);=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorM= sg=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 varchar(512);=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_bnft_hist_actn_ldim';=C2=A0 =C2=A0 =C2= =A0 =C2=A0=C2=A0BEGIN =C2=A0 =C2=A0 DELETE FROM bnft_hist_actn_ldim; =C2=A0 =C2=A0 INSERT INTO bnft_hist_actn_ldim=C2=A0=C2=A0 =C2=A0 SELECT stg= .bnft_hist_actn_id, stg.bnft_hist_actn_src_cd, stg.bnft_hist_actn_desc,=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stg.mig_filename=C2=A0 =C2=A0 FROM stg_bnft= _hist_actn_ldim stg; RETURN NEW; =C2=A0 =C2=A0 v_Ret :=3D SQLSTATE; exception=C2=A0 =C2=A0=C2=A0 =C2=A0when others then v_ErrorCode :=3D SQLSTA= TE;=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorMsg=C2=A0 :=3D SQLERRM;=C2=A0 =C2=A0 = =C2=A0 =C2=A0 v_Ret=C2=A0 =C2=A0 =C2=A0 =C2=A0:=3D v_ErrorCode; PERFORM pr_write_error_log ();=C2=A0=C2=A0 END;$$ LANGUAGE 'plpgsql'; SELECT pr_mig_bnft_hist_actn_ldim(); execute the store funtion no error but two tables are the same and didn't d= elete and/or insert any from one to other; thank you for looking into it. v/r, 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_866008_186433472.1571070451684 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello Experts,
<= div dir=3D"ltr" data-setdir=3D"false" style=3D"font-size: 16px;">
=
Would you= please help me why I can't call the function to delete table and insert da= ta from another table?  
Simple deletion and insert from one to other= are fine 

DELETE FROM= bnft_hist_actn_ldim;

INSERT INTO bnft_hist_actn_ldim 
SELECT stg.bnft_hist_actn_id, stg.bnft_hist_ac= tn_src_cd, stg.bnft_hist_actn_desc, stg.mig_filename
FROM stg_bnft_hist_actn_ldim stg;<= /font>

-----
but when I put = in the stored function, it's not working.

My stor= ed function script is:
=
CREATE OR REPLACE FUNCTION pr_mig_stg_bnft_hist_actn_ldim(OUT v_Ret intege= r)
AS $$

DECLARE 
        v_ErrorCode          =    varchar(32);
=         v_ErrorMsg          &n= bsp;   varchar(512);
=         v_Module          &nbs= p;     varchar(32) =3D 'pr_mig_stg_bnft_hist_actn_ldim';
        <= /font>
BEGIN

    DELETE FROM bnft_hist_actn_ldim;

    INSERT INTO bnft_hist_actn_ldim 
<= font size=3D"2" color=3D"#cd232c">    SELECT stg.bnft_hist_actn_i= d, stg.bnft_hist_actn_src_cd, stg.bnft_hist_actn_desc,      =     stg.mig_filename
    FROM stg_bnft_hist_actn_ldim stg;

RETURN NEW;

    v_Ret :=3D SQLS= TATE;

<= font size=3D"2" color=3D"#cd232c">exception   
   when others then
= = =09v_ErrorCode :=3D SQLSTATE;
        v_ErrorMsg  :=3D SQLERRM;
        v= _Ret       :=3D v_ErrorCode;


=09PERFORM pr_write_error_log ();  <= /font>

END;
$$ LANGUAGE 'plpgsql';

SELECT pr_mig_bnft_hist_actn_ldim();

execute the store funtion no error but two tables a= re the same and didn't delete and/or insert any from one to other;

thank you f= or looking into it.

v/r,

=
Bach-Nga

No one in this world is pure and perfe= ct.  If you avoid people for their mistakes you will be alone. So judg= e 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 tho= se who knew him well, he was a perfect gentleman (Hermione Gingold)<= /span>

**Live simply **Lo= ve generously **Care deeply **Speak kindly.
*** = Genuinely rich *** Faithful talent *** Sharing success
<= /div> ------=_Part_866008_186433472.1571070451684--