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 1iL5ps-00040d-F0 for pgsql-admin@arkaria.postgresql.org; Thu, 17 Oct 2019 13:29:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iL5pq-0000JH-Ol for pgsql-admin@arkaria.postgresql.org; Thu, 17 Oct 2019 13:28:58 +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 1iL5pp-0000J4-SI for pgsql-admin@lists.postgresql.org; Thu, 17 Oct 2019 13:28:58 +0000 Received: from sonic316-11.consmr.mail.bf2.yahoo.com ([74.6.130.121]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iL5pm-00089d-K4 for pgsql-admin@lists.postgresql.org; Thu, 17 Oct 2019 13:28:56 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1571318932; bh=X9jNGAqp/KpEjnnW6ZzPGrToTljvTbEt+MFdJeUFGxM=; h=Date:From:Reply-To:To:In-Reply-To:References:Subject:From:Subject; b=QiMdueqJMAVWkK4KWfibnJm7H9/io6nUQ+dSMbjLEsv0qfj9Q3PWU3ZwOj2Q6gca6Hsh030CG8xZceYzdywy4Q7CJPRsA0+mqh/7lW7Jt4Q4WniCEpH44l+HAIFgeEQPP9Rj9IpBP5nOfqIc1dzPTXuwpG5F9Di3DP3TZ6Rfc8kPvcs5ZvkNYCCLDseH/WZXMj8nk8PALTbnsnXBhDMg8ZPYRX9OvcJ2SxJMjo8y/EgW2dXBEd8C2udZfkr2NNd9+bmMrdAFf2yzHzKoE4QrXKNETIMv/hrh1wZsNJUcxeeogZSo/ujDduLOGAUmlkPuofBNBhhefwwbytn8AwB0DQ== X-YMail-OSG: t4QVocYVM1njBnFuNnrSvcd4J4YT.yEOff6fylIvIH4VSPNZwqjWFLDmgVPDoff EF4.QsswpVz8tlLpVUFP_FoMWgRj7KsZUeMbITAro0Re9tnWMeh18iVAopOrZcYm4QckmzlMx3Qd K2GmYAwS9itELP2rDnvzFPwSBMjMcLk7jOBbYYoQ.aMmHghPoGIsXRkB_ap5YkMp.mr5_Wd256dK sRDMFcyXOfuAmRIHdPtMN44zAaLRtEUT63uupWVxTNuetZA_78.5LtEGscf.lKNYXfR._K17oecc FmEYVrOZN.Mx_ijwlZcM7Mb88zToXNosAk9X6Oz7.X6XL83siJkTKLqkJeJi2gBQVMLE8PhcQ4kr s8KRCTOeYiNeOrVgUeJoo2NJQWMy1b.ruUgWVtZX6bTPm9V1IPON8lpoYfJQweSIbI0HsMeW_s5e GUgnvMhi_p7UoOPbBprAHvSACt3L1uOTKHWmvIFBJSIJYCKlI4tmIzAPFS2hW_.viqfk3ywF3hv6 A0OU5h.S15Y.PgbffiXxOYBcGT1Ip8rhFypDm2kxN1zZKHmNxto7lVREQlrERKXokxzmwI_JHXv9 p5gNbChCqHnUk337ltGIJTbJww.wEXaNw2xhSnzl6XVSuhfWBvvAJZ6KHF8DOQ9CwzOr7jWnuZ4C 3xrEuxBWWqhmP4WvyWurQ30eTIB5Dx1izzPKmjjSEpNxiLg3pkZY73jAs94KP0AHducJECV85ajH Kw7hqE7.QDt3gTg2B2HJoethZF8mw0wjAwAGMesr4_lvD0QEu1C2EMcS_3myEvO2CAu7Q2aCri4R kzytldBhLdAC4TyBofQo4e_.dF.Et0pbe2xu4xpQA1KT4S9aEJe.e02aRO9JHvmx6.pdQqG1GNW5 vgxY5RPFqPY89BQhmIAxSLrpYbQUrX2L6ZLckHF.YPrn2vqIK7sl8xOoV2FoNa5KiWjGMkLeY0eY LwiPuMZZkxrAwkNqDSEEH46SucUoNcdnrs_TczC1MnXdLizT7qx99KpBNfa9NeXDS3TMokmQQI1V StJgU2oVY_LgmhhkDuQR.hd1EBkKBrCZsV5jdHHRQDUfsgQyyGF.FfdQTZM9CnL9DOZpJ6cfTt6A pNj2o.sbUCssoLHoF1yPfaD78V487ccC5v2w_JV6ID8S6.myywtbAev7lCMqX636t0a4ZYfPVK65 IiFQOFNKs0iwyBRP4n1qpkS1wuwl1PjM3EVCowtY2vLauRE2FkI1jtdK1yh9q.TnOVACjkr03rCz ypOYFmUiawPHZ8oGJE_42nAbJJnr0pmLCGxBjWxvgdd__IXZZDSRrAkAjGdTBi50PTC3FZeJtMjw A2WcIZNMjvfJEogC7ZlLeNNyB68euc6xH3ZrFptgEgTQHmjwr.yc- Received: from sonic.gate.mail.ne1.yahoo.com by sonic316.consmr.mail.bf2.yahoo.com with HTTP; Thu, 17 Oct 2019 13:28:52 +0000 Date: Thu, 17 Oct 2019 13:28:49 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: pgsql-admin@lists.postgresql.org, Holger Jakobs Message-ID: <1316662871.1944417.1571318929175@mail.yahoo.com> In-Reply-To: <9498a9ae-2025-1d63-2f0a-6ab1baaa1520@jakobs.com> References: <292566289.866009.1571070451686.ref@mail.yahoo.com> <292566289.866009.1571070451686@mail.yahoo.com> <9498a9ae-2025-1d63-2f0a-6ab1baaa1520@jakobs.com> Subject: Re: can't call function to delete the table MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1944416_1464208435.1571318929171" 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: 21761 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1944416_1464208435.1571318929171 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable thank you for all input.=C2=A0=C2=A0I made it work already. many thanks again. 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=20 On Thursday, October 17, 2019, 04:30:40 AM EDT, Holger Jakobs wrote: =20 =20 =20 Hello Pepe, =20 RETURN NEW; is a statement only for functions which are declared with RETUR= NS TRIGGER and are used by triggers. =20 Even then no statement after the RETURN NEW; would ever be executed, so it = remains unclear what v_Ret :=3D SQLSTATE; i is supposed to do. =20 Regards, =20 Holger =20 =20 Am 14.10.19 um 18:27 schrieb Pepe TD Vo: =20 =20 Hello Experts,=20 Would you please help me why I can't call the function to delete table an= d insert data from another table?=C2=A0=C2=A0 Simple deletion and insert fr= om one to other are fine=C2=A0=20 DELETE FROM bnft_hist_actn_ldim;=20 INSERT INTO bnft_hist_actn_ldim=C2=A0 SELECT stg.bnft_hist_actn_id, stg.b= nft_hist_actn_src_cd, stg.bnft_hist_actn_desc, stg.mig_filename FROM stg_bn= ft_hist_actn_ldim stg;=20 ----- but when I put in the stored function, it's not working.=20 My stored function script is: =20 CREATE OR REPLACE FUNCTION pr_mig_stg_bnft_hist_actn_ldim(OUT v_Ret int= eger) AS $$=20 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_Erro= rMsg=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=A0 BEGIN=20 =C2=A0 =C2=A0 DELETE FROM bnft_hist_actn_ldim;=20 =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;=20 RETURN NEW;=20 =C2=A0 =C2=A0 v_Ret :=3D SQLSTATE;=20 exception=C2=A0 =C2=A0 =C2=A0 =C2=A0when others then v_ErrorCode :=3D SQ= LSTATE; =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;=20 =20 PERFORM pr_write_error_log ();=C2=A0=C2=A0=20 END; $$ LANGUAGE 'plpgsql'; =20 SELECT pr_mig_bnft_hist_actn_ldim();=20 execute the store funtion no error but two tables are the same and didn't= delete and/or insert any from one to other;=20 thank you for looking into it.=20 v/r, =20 =20 Bach-Nga =20 No one in this world is pure and perfect.=C2=A0 If you avoid people for th= eir mistakes you will be alone. So judge less, love and forgive more. To ca= ll him a dog hardly seems to do him justice though in as much as he had fou= r 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)= =20 **Live simply **Love generously **Care deeply **Speak kindly. *** Genuinel= y rich *** Faithful talent *** Sharing success --=20 =20 Holger Jakobs, Bergisch Gladbach instant messaging: xmpp:holger@jakobs.com +49 178 9759012 oder +49 2202 817157=20 =20 ------=_Part_1944416_1464208435.1571318929171 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
thank you for all input.  
=
I made it work already.

many thanks again.

v/r,

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 hi= m justice though in as much as he had four legs, a tail, and barked, I admi= t he was, to all outward appearances. But to those who knew him well, he wa= s a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deepl= y **Speak kindly.
*** Genuinely rich *** Fa= ithful talent *** Sharing success


=20
=20
On Thursday, October 17, 2019, 04:30:40 AM EDT, Holger = Jakobs <holger@jakobs.com> wrote:


Hello Pepe,

RETURN NEW; is a statement only for functions which are declared with RETURNS TRIGGER and are used by triggers.

Even then no statement after the RETURN NEW; would ever be executed, so it remains unclear what v_Ret :=3D SQLSTATE; i is supposed to do.

Regards,

Holger


Am 14.10.19 um 18:27 schrie= b Pepe TD Vo:
Hello Experts,

Would you please help me why I can't call the function to delete table and insert data from another table?  
Simple deletion and insert from one to other are fine 

DELETE FRO= M bnft_hist_actn_ldim;

INSERT INT= O bnft_hist_actn_ldim 
SELECT stg.bnft_hist_actn_id, stg.bnft_hist_actn_src_cd, stg.bnft_hist_actn_desc, stg.mig_filename
FROM stg_bnft_hist_actn_ldim stg;

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

My stored function script is:

CREATE OR = REPLACE FUNCTION pr_mig_stg_bnft_hist_actn_ldim(OUT v_Ret integer)
AS $$

DECLARE <= /div>
      =   v_ErrorCode             varchar(32);
      =   v_ErrorMsg              varchar(512);
      =   v_Module                varchar(32) =3D 'pr_mig_stg_bnft_hist_actn_ldim';
      =   
BEGIN

    DELETE = FROM bnft_hist_actn_ldim;

    INSERT = INTO bnft_hist_actn_ldim 
    SELECT stg.bnft_hist_actn_id, stg.bnft_hist_actn_src_cd, stg.bnft_hist_actn_desc,       =   stg.mig_filename
    FROM st= g_bnft_hist_actn_ldim stg;

RETURN NEW;

    v_Ret := =3D SQLSTATE;

exception   = ;
   when oth= ers then
=09v_ErrorCode :=3D SQLSTATE;
      =   v_ErrorMsg  :=3D SQLERRM;
      =   v_Ret       :=3D v_ErrorCode;


=09PERFORM pr_write_error_log ();  

END;
$$ LANGUAGE 'plpgsql';
<= br clear=3D"none">
SELECT pr_mig_bnft_hist_actn_ldim();

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

thank you for look= ing into it.

v/r,


<= /span>
Bach-Nga

No one in this world is pure and perfect.  I= f 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
=20
--

Holger Jakobs, Bergisch Gladbach
instant messaging: xmpp:holger@j= akobs.com
+49 178 9759012 oder +49 2202 817157

------=_Part_1944416_1464208435.1571318929171--