Re: can't call function to delete the table

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 := SQLSTATE; i is supposed to do.

Regards,

Holger


Am 14.10.19 um 18:27 schrieb 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 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 stg_bnft_hist_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 
>         v_ErrorCode             varchar(32);
>         v_ErrorMsg              varchar(512);
>         v_Module                varchar(32) =
> '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 stg_bnft_hist_actn_ldim stg;
>
> RETURN NEW;
>
>     v_Ret := SQLSTATE;
>
> exception   
>    when others then
> v_ErrorCode := SQLSTATE;
>         v_ErrorMsg  := SQLERRM;
>         v_Ret       := v_ErrorCode;
>
>
> PERFORM pr_write_error_log ();  
>
> 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 delete 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.  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:[email protected]
+49 178 9759012 <tel:+491789759012> oder +49 2202 817157
<tel:+492202817157>